Learn about the most essential SQL Server data types
Published: Jan 15, 2025
In this article we’ll cover the essential SQL Server data types and discuss their common usage.
Data is at the core of all relational database systems and SQL Server supports many different types of data and each data type represents a specific category of data. Each of the different data types have different uses and permitted operations. For example, character data types might support string searches and comparisons while numeric data types support mathematical operations.
The most common SQL Server data types are probably the different character data types. SQL Server character data types can contain character strings like letters, numbers, and special characters. Character data types support string searches, comparisons and substring operations.
The primary character data types are char and varchar. Char data types are fixed length while varchars are variable length. For a char data type the (n) represents the actual length of the character data for a varchar it is the maximum length.
The nchar and nvarchar data types are the same as the char and varchar data types but store Unicode data. The text data type is used for very large character string data and has a maximum length of 2 GB.
Data Type | Description |
char(n) | A fixed length string where n specifies the column length from 1 to 8,000. Default size is 1. |
varchar(n) | A variable length string where n specifies the maximum string length from 1 to 8,000. Default size is 1. |
varchar(max) | A variable string up to 2 GB |
text | Variable length non-Unicode character data up to 2 GB. Depreciated but commonly used. |
nchar(n) | A fixed length Unicode string where n specifies the column length from 1 to 4,000. Default size is 1. |
nvarchar(n) | A variable length Unicode string where n specifies the maximum string length from 1 to 4,000. Default size is 1. |
nvarchar(max) | Variable width Unicode character data up to 2 GB |
ntext | Variable width Unicode character data up to 2 GB. Depreciated but commonly used. |
Like you might expect, the SQL Server numeric data types are used to store various types of numbers and they are used for mathematical operations. The different numeric data types support different degrees of decimal precision.
The tinyint, smallint, int and bigint are used for integers, which are whole numbers with no decimals. The decimal, numeric, float and real are all used for floating point number with decimals. The decimal and numeric data types are functionally equivalent. The money and smallmoney data types are specially used for currency.
Data Type | Description |
tinyint | Whole numbers from 0 to 255 |
smallint | Whole numbers between -32,768 and 32,767 |
int | Whole numbers between -2,147,483,648 and 2,147,483,647 |
bigint | Whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
decimal(p,s) | Fixed precision and scale numbers from -10^38 +1 to 10^38 –1. Functionally identical to numeric. |
numeric(p,s) | Fixed precision and scale numbers from -10^38 +1 to 10^38 –1. Functionally identical to decimal. |
float(n) | Floating precision number data from -1.79E + 308 to 1.79E + 308 |
real | Floating precision number data from -3.40E + 38 to 3.40E + 38 |
money | Currency from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
smallmoney | Currency from -214,748.3648 to 214,748.3647 |
SQL Server also provides a number of different data types that are specially designed to store date and time related data. The datetime data type stores both date and time data together. While datetime2 extents the range and accuracy of the stored date/time data.
The datetimeoffset data type includes time zone data. Like you might guess, the date data type stores just dates while the time data type stores just times.
Data Type | Description |
datetime | From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds. The default format is yyyy-mm-dd hh:mm:ss |
datetime2 | From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
smalldatetime | From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute |
date | Dates from January 1, 0001 to December 31, 9999 |
time | Times to an accuracy of 100 nanoseconds |
datetimeoffset | The same as datetime2 with the addition of a time zone offset |
SQL Server also supports several other common data types that are used for special purposes. The rowversion data type is a unique counter that’s used to version stamp a row. It has superseded the older timestamp data type. The binary and varbinary data types store binary strings of data up to their supported sizes.
The uniqueidenifier data type is often used to ensure each row has a unique identifier. The xml data type was introduced to store and facilitate processing xml data. The bit data type is mainly used for binary on-off (1, 0) designations. Like its name implies, the image data type was traditionally used to store image data which have also been called blobs but it has been depreciated.
Images are now often stored using the Filestream data type where the actual image storage resides in the file system – not the relational database.
Data Type | Description |
rowversion | An incrementing number used for version-stamping rows |
binary (n) | Stores any kind of fixed size binary data where n is from 1 to 8,000 |
varbinary(n) | Stores any kind of variable size binary data where n is from 1 to 8,000 |
varbinary (max) | Stores up to 2GB of variable sized binary data |
uniqueidentifier | Stores a 16-byte globally unique identifier (GUID) |
xml | Stores XML formatted data up to 2GB |
bit | Values 0, 1, and NULL |
image | Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. Depreciated but commonly used. |
In this tutorial you learned about the most essential SQL Server data types. The data types covered in this article are found in almost every SQL Server database. In addition to the core data types that are listed in this article, SQL Server supports several other specialized and somewhat less common data types.
Some of the other less common data types include table, Filestream, spatial geography type, hierarchyid and others. You can learn more about the supported SQL Server data types at Transact-SQL reference (Database Engine).