Relational Databases 101—Working with Imprecise Numbers
Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio
| CSharp-Online.NET:Articles |
| Database Articles |
| © 2007 Pearson Education, Inc. |
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.
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. |
 
|
