• Article

Data Types (Database Engine)

Objects that contain data have an associated data type that defines the kind of data; for example, character, integer, or binary, the object can contain. The following objects have data types:

  • Columns in tables and views.

  • Parameters in stored procedures.

  • Variables.

  • Transact-SQL functions that return one or more data values of a specific data type.

  • Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

  • The kind of data contained by the object.

  • The length or size of the stored value.

  • The precision of the number (numeric data types only).

  • The scale of the number (numeric data types only).

For more information about data type precision, scale and length, see Precision, Scale, and Length (Transact-SQL).

Transact-SQL has these system data types. 

bigint

binary

bit

char

CLR

cursor

date

datetime

datetime2

datetimeoffset

decimal

float

hierarchyid

image

int

money

nchar

ntext

numeric

nvarchar

real

rowversion

smalldatetime

smallint

smallmoney

sql_variant

table

text

time

timestamp

int, bigint, smallint, and tinyint (Transact-SQL)

varbinary

varchar

uniqueidentifier

xml (Transact-SQL)

All data stored in Microsoft SQL Server must be compatible with one of these base data types. The cursor data type is the only system data type that cannot be assigned to a table column. It can be used only with variables and stored procedure parameters.

Several base data types have synonyms (for example, rowversion is a synonym for timestamp, and national character varying is a synonym for nvarchar). For more information about the behavior of synonyms, see Data Type Synonyms (Transact-SQL).

Two kinds of user-defined data types can also be created:

  • Alias data types are created from base data types. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. For example:

    -- Create a birthday datetype that allows nulls.
CREATE TYPE birthday
FROM datetime NULL
GO
-- Create a table using the new data type.
CREATE TABLE employee (emp_id char(5), emp_first_name char(30), emp_last_name char(40), emp_birthday birthday)

    For more information, see Working with Alias Data Types.

  • CLR user-defined data types are based on data types created in managed code and uploaded in a SQL Server assembly. For more information, see Working with CLR User-defined Types.