Data Types

Data Types


As we all know the selection of proper data types is very important for a SQL Server Table objects. Without proper data types the performance is going down and it is un manageable for SQL developer.


In this article I am trying to gather and provide a chart that contains most useful data types used in Microsoft SQL Server Version 2000/2005/2008.
Datatype
Min
Max
Storage
Type
Notes
Bigint
-2^63
2^63-1
8 bytes
Exact numeric
Int
-2,147,483,648
2,147,483,647
4 bytes
Exact numeric
Smallint
-32,768
32,767
2 bytes
Exact numeric
Tinyint
0
255
1 bytes
Exact numeric
Bit
0
1
1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc…
Exact numeric
Decimal
-10^38+1
10^38–1
Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes 
Exact numeric
Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric
no
Money
-2^63 / 10000
2^63-1 / 10000
8 bytes
Exact numeric
Smallmoney
-214,748.3648
214,748.3647
4 bytes
Exact numeric
Float
-1.79E + 308
1.79E + 308
4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53
Approximate numeric
Precision is specified from 1 to 53.
Real
-3.40E + 38
3.40E + 38
4 bytes
Approximate numeric
Precision is fixed to 7.
Datetime
1753-01-01 00:00:00.000
9999-12-31 23:59:59.997
8 bytes
Date and time
If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime 
1900-01-01 00:00
2079-06-06 23:59
Date and time
Date
 (SQL2008)
0001-01-01
9999-12-31
Date and time
Time
(SQL2008)
00:00:00.0000000
23:59:59.9999999
Date and time
Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2
(SQL2008)
0001-01-01 00:00:00.0000000
9999-12-31 23:59:59.9999999
Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes
Date and time
Combines the date data type and the time data type into one. The precision logic is the same as for the time data type.
Datetimeoffset
(SQL2008)
0001-01-01 00:00:00.0000000 -14:00
9999-12-31 23:59:59.9999999 +14:00
Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes
Date and time
Is a datetime2 data type with the UTC offset appended.
Char
0 chars
8000 chars
Defined width
Character string
Fixed width
Varchar
0 chars
8000 chars
2 bytes + number of chars
Character string
Variable width
Varchar(max)
(SQL2005/2008)
0 chars
2^31 chars
2 bytes + number of chars
Character string
Variable width
Text
0 chars
2,147,483,647 chars
4 bytes + number of chars
Character string
Variable width
Nchar
0 chars
4000 chars
Defined width x 2
Unicode character string
Fixed width
Nvarchar
0 chars
4000 chars
Unicode character string
Variable width
Nvarchar(max)
(SQL2005/2008)
0 chars
2^30 chars
Unicode character string
Variable width
Ntext
0 chars
1,073,741,823 chars
Unicode character string
Variable width
Binary
0 bytes
8000 bytes
Binary string
Fixed width
Varbinary
0 bytes
8000 bytes
Binary string
Variable width
Varbinary(max)
(SQL2005/2008)
0 bytes
2^31 bytes
Binary string
Variable width
Image
0 bytes
2,147,483,647 bytes
Binary string
Variable width
Sql_variant
Other
Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
Timestamp
Other
Stores a database-wide unique number that gets updated every time a row gets updated.
Uniqueidentifier
Other
Stores a globally unique identifier (GUID).
Xml
(SQL2005/2008)
Other
Stores XML data. You can store xml instances in a column or a variable.
Cursor
Other
A reference to a cursor.
Table
Other
Stores a result set for later processing.

Hope you like it.

Posted by: MR. JOYDEEP DAS