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.
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.

Popular