SQL Server Data Types Reference
Do you know exactly every data type in SQL Server and their usage and storage requirements? If not, look them all up in this reference sheet.
Introduction
There are plenty of data types to use in SQL Server. Knowing the limitations and benefits of each one will soon pay off. For instance, choosing the tinyint datatype instead of the int datatype for the "ProductType" column with values ranging from 1 to 10 will save three bytes per record. With 100,000 records you will save 300,000 bytes. That's not much in terms of disc space ("storage is cheap, bla bla") but you'll probably have indexes containing that column and if that index takes less memory the engine will process that index much faster in every "join" and "where" etc. So those queries will perform better and use less system resources (memory) which will make the whole server perform better as there will be more resources available for other things. Once learned the datatypes available and spending a few extra minutes when designing your schema will result in faster query execution and a better performing database.
This sheet provides an easy reference to look up limitations and benefits for each SQL Server data type.
The Data types reference sheet
The columns 8, 9, 10 shows what versions of SQL Server that supports the data type
· 8 = SQL Server 2000
· 9 = SQL Server 2005
· 10 = SQL Server 2008
|
Datatype |
Min |
Max |
Storage |
8 |
9 |
10 |
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 booth 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 numerics |
Precision is specified from 1 to 53. |
|||
|
Real |
-3.40E + 38 |
3.40E + 38 |
4 bytes |
Approximate numerics |
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 |
0001-01-01 |
9999-12-31 |
no |
no |
Date and time |
|||
|
Time |
00:00:00.0000000 |
23:59:59.9999999 |
no |
no |
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 |
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 |
no |
no |
Date and time |
Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype. |
|
|
Datetimeoffset |
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 |
no |
no |
Date and time |
Is a datetime2 datatype 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) |
0 chars |
2^31 chars |
2 bytes + number of chars |
no |
Character string |
Variable width |
||
|
Text |
0 chars |
2,147,483,647 chars |
4 bytes + number of chars |
Character string |
Variable width |
|||
|
Related Articles
Free Download Location for Microsoft SQL Server Management Studio
Microsoft has published a new version of SQL Server database management tools - SQL Server...
Connection Strings Explained
Connection Strings Explained
Introduction
When your application connects to a database or a...
Formatting Rules for Connection Strings
Formatting Rules for Connection Strings
As a string, there are no built in checks or...
All SQL Server SqlConnection properties
All SQL Server SqlConnection properties
There are plenty of combinations when creating an SQL...
Store connection string in web.config
Store connection string in web.config
It is very easy to store the connection string in a...
|