Loading ad...
SQL Server Data Types Reference
by Max Wikström
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.
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 |
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) | 0 chars | 2^30 chars |
|
no |
|
|
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) | 0 bytes | 2^31 bytes |
|
no |
|
|
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 |
|
|
|
no |
|
|
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. |
Summary
Spend some time studying these data types. Correctly used data types will improve performance, save storage on disk and reduce backup times. It will also help in providing a consistent structure. Choosing data types is indeed an important part of constraining a database schema.