SQL Server Data Types

In SQL Server, data type specifies the type of data that can be stored in a column of a table such as integer data, string data, date & time, binary strings, etc.

Assigning an appropriate data type to columns in a table is crucial while designing a database. It affects the performance and efficiency of the database and the application using the database.

SQL Server provides built-in data types for all kinds of data that can be used within SQL Server. Additionally, you can also define your own data type in T-SQL.

Data Type Categories:

Category Data Types
Exact numerics bit, tinyint, smallint, int, bigint, decimal, numeric, money, smallmoney
Approximate numerics Real, Float
Date & Time date, smalldatetime, datetime, datetime2, datetimeoffset, time
Character strings char, varchar, text
Unicode Character strings nchar, nvarchar, ntext
Other data types cursor, hierarchyid, sql_variant, spatial Geometry types, spatial Geography types, rowversion, uniqueidentifier, xml, table

Exact Numerics

Data Type Value Description
bit 0,1 or NULL Smallest data type of 1 byte storage size
tinyint 0 to 255 1 bytes storage size
smallint -32,768 to 32,767 2 bytes storage size
int −2,147, 483,648 4 bytes storage
bigint −9,223,372, 036,854,775,808 8 bytes storage
decimal −10^38+1 to 10^38−1 Numeric data type that have a fixed precision and scale.
smallmoney -214,748.3648 to 214,748.3647 4 bytes
money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes

Approximate Numerics

Data Type Value Description
float(n) - 1.79E+308 to -2.23E-308, 0 Storage depends on the value of n.
real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 bytes

Date and Time

Data Type Value Description
date 0001-01-01 through 9999-12-31 3 bytes
datetime Date: 1753-01-01 through 9999-12-31 8 bytes
datetime2 Date: 0001-01-01 through 9999-12-31 Precision < 3 : 6 bytes
smalldatetime 1900-01-01 through 2079-06-06 4 bytes fixed
datetimeoffset Date: 0001-01-01 through 10 bytes fixed
time 00:00:00.0000000 through 23:59:59.9999999 5 bytes fixed

Character Strings

Data Type Value Description
char[(n)] 1 to 8000 characters n bytes
varchar[(n)] 1 to 8000 n bytes + 2 bytes
varchar(max) 1 to 2^31-1 n bytes + 4 bytes
text 0 to 2,147,483,647 n bytes + 4 bytes

Unicode Character Strings

Data Type Value Description
nchar[(n)] 1 to 4000 characters 2 times n bytes
nvarchar[(n|max)] 1 to 4000 2 times n bytes
ntext 0 to 1,073,741,823 2 times the string length

Binary Strings

Data Type Value Description
binary[(n)] 1 to 8000 bytes n bytes
varbinary[(n|max)] 1 to 8000 Actual length of string + 2 bytes
Image 0 to 2,147,483,647 Variable length binary data

Other Data Types

Data type Description
cursor A data type for variables or stored procedures OUTPUT parameters that contain a reference to a cursor.
rowversion It returns automatically generated unique binary numbers within a database.
hierarchyid It is a variable length system data type.
uniqueidentifier Is a 16 byte GUID
sql_variant A data type that stores values of various SQL Server supported data types.
xml It stores xml data.
Spatial Geometry type Used to represent data in a flat coordinate system (Euclidean).
table It is a special data type used to store a result-set temporarily for processing at a later time.
Want to check how much you know SQL Server?