T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - Data Types



T-SQL (Transact-SQL) Data Type is an attribute which specifies the type of data that will be stored inside each column when creating a table. The data type is a guideline for T-SQL to understand what type of data is expected inside of each column, and it also identifies how T-SQL will interact with the stored data.

In T-SQL (Transact-SQL), the data types can be mainly classified into four categories:

  • String Data Types
  • Numeric Data Types
  • Date and time Data Types
  • Other Data Types

String Data Types

Data typeDescription
CHAR(size)A fixed width character string data type. Its size can be up to 8000 characters.
VARCHAR(size)A variable width character string data type. Its size can be up to 8000 characters.
VARCHAR(max)A variable width character string data types. Its size can be up to 231-1 characters.
TEXTA variable width character string data type. Its size can be up to 2GB of text data.
NCHAR(size)A fixed width Unicode string data type. Its size can be up to 4000 characters.
NVARCHAR(size)A variable width Unicode string data type. Its size can be up to 4000 characters.
NVARCHAR(max)A variable width Unicode string data type. Its size can be up to 231-1 characters.
NTEXTA variable width Unicode string data type. Its size can be up to 2GB of text data.
BINARY(size)A fixed Binary string data type. Its size can be up to 8000 bytes.
VARBINARY(size)A variable width Binary string data type. Its size can be up to 8000 bytes.
VARBINARY(max)A variable width Binary string data type. Its size can be up to 231-1 characters.
IMAGEA variable width Binary string data type. Its size can be up to 2GB.

Numeric Data Types

Data typeDescription
BITAn integer that can be 0, 1 or null.
TINYBITA whole numbers from 0 to 255.
SMALLINTA whole numbers between -32,768 and 32,767.
INTA whole numbers between -2,147,483,648 and 2,147,483,647.
BIGINTA whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
FLOAT(n)A floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.
REALA floating precision number data from -3.40E+38 to 3.40E+38.
SMALLMONEYUsed to specify monetary data from - 214,748.3648 to 214,748.3647.
MONEYUsed to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.
DECIMAL(p, s)A Fixed precision and scale numbers. The maximum total number of decimal digits to be stored is specified by p. The number of decimal digits that are stored to the right of the decimal point is specified by s. When maximum precision is used, valid values are from - 1038 +1 through 1038 - 1.
DEC(p, s)Identical to DECIMAL(p, s).
NUMERIC(p, s)Identical to DECIMAL(p, s).

Date and Time Data Type

Data typeDescription
DATETIMEA date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.mmm]'. It supports range from January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds.
DATETIME2A date and time combination. Format: 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
DATEA date. Format: 'YYYY-MM-DD'. It supports range from January 1, 0001 to December 31, 9999.
TIMEA time. Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'. Used to store time only to an accuracy of 100 nanoseconds. Values range from '00:00:00.0000000' to '23:59:59.9999999'.
DATETIMEOFFSETA date and time combination and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time). Format: 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
SMALLDATETIMEA date and time combination. Format: 'YYYY-MM-DD hh:mm:ss'. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.

Other Data Types

Data typeDescription
sql_variantUsed for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data.
XMLStores XML formatted data. Maximum 2GB.
cursorStores a reference to a cursor used for database operations.
hierarchyidA variable length, system data type. Used to represent position in a hierarchy.
rowversionGenerally used as a mechanism for version-stamping table rows. The storage size is 8 bytes.
tableStores result set for later processing.
uniqueidentifierStores GUID (Globally unique identifier).
Spatial Geometry TypesUsed to represent data in a flat coordinate system.
Spatial Geography TypesUsed to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.