Working with Imprecise Numbers
When working with scientific data where you need more precision but not 100% accuracy (which sounds a bit strange), you can choose the approximate number data types. Sure, some numbers can be expressed exactly, but others can't due to binary round-off. In the case of the float datatype, you can define the precision and storage size by providing a value that determines the number of bits used to store the mantissa14 of the floating point number (in scientific notation). If you supply a value between 1 and 24, the float's precision is set to 7, and it takes 4 bytes to store the value. If you provide a value between 25 and 53, the float's precision is set to 15, and it takes 8 bytes to store the value. The default is 53. Note that SQL Server 2005 resets the mantissa setting to either 1 or 53, based on the value you supply.
14 Mantissa: the fractional part of a floating-point number.
Table 3.1 SQL Server Datatypes and Their Precision
|
Datatype |
Bytes |
|
Exact Numerics |
|
|
These values are stored so the value stored is expressed exactly—they are not subject to binary round-off. |
Integers |
bigint |
8 |
Integer (whole number) data from –2^63 (–9223372036854775808) through 2^63–1 (9223372036854775807). |
|
int |
4 |
Integer r(whole number) data from –2^31 (–2,147,483,648) through 2^31 – 1 (2,147,483,647). |
|
smallint |
2 |
Integer data from 2^15 (–32,768) through 2^15 –1 (32,767). |
|
tinyint |
1 |
Integer data from 0 through 255. |
Bit |
bit |
1 |
Integer data with either a 1 (True), 0 (False), or NULL value. |
Decimal |
decimal |
5–17 |
Fixed precision and scale numeric data from –10^38 +1 through 10^38 –1. |
|
numeric |
|
Functionally equivalent to decimal. |
Money |
money |
4 |
Monetary data values from –2^63 (–922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. |
|
smallmoney |
8 |
Monetary data values from –214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. |
Approximate Numerics |
|
|
These values are stored in binary and are used when a precise but not 100% accurate value must be stored. |
|
float |
4–8 |
Floating precision number data from –1.79E + 308 through 1.79E + 308. |
|
doubleprecision |
8 |
Equivalent to float(53) (8 bytes). |
|
real |
4 |
Floating precision number data from –3.40E + 38 through 3.40E + 38. |
Dates |
datetime |
8 |
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. |
|
smalldatetime |
4 |
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of 1 minute. |
ANSI Character Strings |
|
|
These values are stored as strings of characters in non-Unicode (ANSI) encoding (8-bits/character). |
|
char |
N |
Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
|
varchar |
N |
Variable-length non-Unicode data with a maximum of 8,000 characters. |
|
varchar(max) |
N |
Variable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters. |
|
text |
N |
Variable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters. |
Unicode Character Strings |
|
|
These values are stored in Unicode (16-bits/character). |
|
nchar |
N |
Fixed-length Unicode data with a maximum length of 4,000 characters; 16 bits stored for each character. |
|
nvarchar |
N |
Variable-length Unicode data with a maximum length of 4,000 characters. |
|
sysname |
128 |
System-supplied user-defined data type that is functionally equivalent to nvarchar (128) and is used to reference database object names. |
|
nvarchar(max) |
|
Variable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters. |
|
ntext |
N |
Variable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters. |
Binary Strings |
|
|
These values are stored in binary with any attempt to encode them. |
|
binary |
N |
Fixed-length binary data with a maximum length of 8,000 bytes. |
|
varbinary |
N |
Variable-length binary data with a maximum length of 8,000 bytes. |
|
varbinary(max) |
|
Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes. |
|
image |
N |
Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes. |
Other Types |
|
|
|
|
cursor |
— |
A reference to a server-side CURSOR. |
|
sql_variant |
N |
A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant. |
|
table |
— |
A special data type used to store a rowset for later processing. |
|
timestamp |
8 |
A database-wide unique number that gets updated every time a row gets updated. |
|
uniqueidentifier |
16 |
A globally unique identifier (GUID). |
|
xml |
N |
Names an XML schema collection. Can store up to 2GB of data. |
Using the xml Datatype
For the first time, SQL Server 2005 introduces the new xml datatype. This means you're going to be able to store XML data in your table's column(s). Because xml is a "real" built-in type, you'll be able to use it when creating a table as a variable type, a parameter type, or a function return type. You'll also be able to use it in CAST or CONVERT. That said, I need to discuss where it makes sense to use xml typed data columns or xml typed arguments. One interesting use would permit you to pass lists of values to be used in an IN expression. Yes, you would need to write a function to convert this to a table-type variable.
Using the sql_variant Datatype
One of SQL Server 2000's innovations was "lifted" from Visual Basic—the "variant." The sql_variant datatype is unusual, in that it's designed to "morph" itself to most (non-BLOB) types. This means when you define a column as sql_variant, it can contain an integer (of any size), a string, a float, money, or even an xml structure. The sql_variant column value does not take on a type until you assign a value to it. I suggest you check out BOL for the rules and regulations involving this unique type.
