Data Types (Transact-SQL)

In SQL Server 2005, 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 Working with Alias Data Types. 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. For more information, see Working with CLR User-defined Types.

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:

  • The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. For more information, see Data Type Precedence (Transact-SQL).
  • The collation of the result is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL).
  • The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. For more information, see Precision, Scale, and Length (Transact-SQL).

SQL Server 2005 provides data type synonyms for SQL-92 compatibility. For more information, see Data Type Synonyms (Transact-SQL).

Data Type Categories

Data types in SQL Server 2005 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 2005, based on their storage characteristics, some data types are designated as belonging to the following groups:

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

Note

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

Exact Numerics

bigint

decimal

int

numeric

smallint

money

tinyint

smallmoney

bit

 

Approximate Numerics

float

real

Date and Time

datetime

smalldatetime

Character Strings

char

text

varchar

 

Unicode Character Strings

nchar

ntext

nvarchar

 

Binary Strings

binary

image

varbinary

 

Other Data Types

cursor

timestamp

sql_variant

uniqueidentifier

table

xml

See Also

Reference

CREATE PROCEDURE (Transact-SQL)
CREATE TABLE (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
EXECUTE (Transact-SQL)
Expressions (Transact-SQL)
Functions (Transact-SQL)
LIKE (Transact-SQL)
SET (Transact-SQL)
sp_bindefault (Transact-SQL)
sp_bindrule (Transact-SQL)
sp_droptype (Transact-SQL)
sp_help (Transact-SQL)
sp_rename (Transact-SQL)
sp_unbindefault (Transact-SQL)
sp_unbindrule (Transact-SQL)

Other Resources

Working with CLR User-defined Types
Using Unicode Data

Help and Information

Getting SQL Server 2005 Assistance