SQL Data Types

A Data Type defines the type of data that can be stored in a table column.

Examples include: integer, date, money, string, decimal, binary, and others.

Data Type Categories

#

SQL Server data types fall into five categories.

Numeric Types

These data types store numbers and numberic data.

Data Type Description
BIT Boolean type with values 0 (false), 1 (true), or NULL. Default is 1.
TINYINT Tiny whole numbers from 0 to 255.
SMALLINT Small whole numbers from -32,768 to 32,768.
INT Whole numbers from -2,147,483,648 to 2,147,483,648.
BIGINT Large whole numbers from -9,223,372,036, 854,775,808 to 9,223,372,036, 854,775,808.
DECIMAL Fixed-point and scale number from -10^38 +1 to 10^38 +1.
NUMERIC Same as decimal. Fixed-point and scale from -10^38 +1 to 10^38 +1.
FLOAT Floating point number from -1.79E + 308 to 1.79E + 308.
REAL Floating point number from -3.40E + 38 to 3.40E + 38.
SMALLMONEY Monetary value from -214,748.3648 to 214,748.3647.
MONEY Monetary value from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

Date and Time Types

These types store datetime, date, and time data.

Data Type Description
DATE Date in YYYY-MM-DD format.
Supported range is from 1000-01-01 to 9999-12-31.
TIME Time in hh:mm:ss[.nnnnnnn] format.
Supported range is from 00:00:00.0000000 to 23:59:59.9999999.
DATETIME2 Date and time in YYYY-MM-DD hh:mm:ss[.nnnnnnn] format.
Date range is 0001-01-01 to 9999-12-31.
Time range is 00:00:00 to 23:59:59.9999999.
DATETIMEOFFSET Datetime with timezone in YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] format.
Date range is 0001-01-01 to 9999-12-31.
Time range is 00:00:00 to 23:59:59.9999999.
Timezone range is -14:00 to +14:00.
SMALLDATETIME Date and time in YYYY-MM-DD hh:mm:ss format.
Date range is 1900-01-01 to 2079-06-06.
Time range is 00:00:00 to 23:59:59.
DATETIME Date and time in YYYY-MM-DD hh:mm:ss[.nnn] format.
Date range is 1753-01-01 to 9999-12-31.
Time range is 00:00:00 to 23:59:59.997.

Character and String Types

These types store text data.

Data Type Description Max Size
CHAR Fixed length character string 8,000 characters
NCHAR Fixed length Unicode string 4,000 characters
VARCHAR Variable-length character string 8,000 characters
NVARCHAR Variable-length Unicode string 4,000 characters
VARCHAR(Max) Variable-size character storage
The max indicates maximum characters
2GB of data
NVARCHAR(Max) Variable-size Unicode storage
The max indicates maximum characters
2GB of data
TEXT Variable-length character string 2GB of data
NTEXT Variable-length Unicode string 1GB of data

Binary Types

These types store binary data.

Data Type Description Max Size
BINARY Fixed length binary string 8,000 bytes
VARBINARY Variable length binary storage 8,000 bytes
VARBINARY(Max) Variable length binary storage
The max indicates maximum characters
2GB of data
IMAGE Variable length binary image storage 2GB of data

Miscellaneous Types

These are other types supported by SQL Server.

Data Type Description Max Size
ROWVERSION Binary unique number. Used for version stamping. 8 bytes
UNIQUEIDENTIFIER An autogenerated GUID (globally unique identifier). 16 bytes
XML Stores XML data 2GB of data

You may also like



Guides


vsn 3.1