Data Types (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types. For more information about alias data types, see CREATE TYPE (Transact-SQL). User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework.

When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following:

Data Type Categories

Data types in SQL Server are organized into the following categories:

Exact numerics Unicode character strings
Approximate numerics Binary strings
Date and time Other data types
Character strings

In SQL Server, based on their storage characteristics, some data types are designated as belonging to the following groups:

  • Large value data types: varchar(max), and nvarchar(max)

  • Large object data types: text, ntext, image, varbinary(max), and xml


    sp_help returns -1 as the length for the large-value and xml data types.

Exact Numerics

bigint numeric
bit smallint
decimal smallmoney
int tinyint

Approximate Numerics

float real

Date and Time

date datetimeoffset
datetime2 smalldatetime
datetime time

Character Strings

char varchar

Unicode Character Strings

nchar nvarchar

Binary Strings

binary varbinary

Other Data Types

cursor timestamp
hierarchyid uniqueidentifier
sql_variant xml
table Spatial Types

See Also

DECLARE @local_variable (Transact-SQL)
EXECUTE (Transact-SQL)
Expressions (Transact-SQL)
Functions (Transact-SQL)
LIKE (Transact-SQL)
sp_droptype (Transact-SQL)
sp_help (Transact-SQL)
sp_rename (Transact-SQL)