SQL Server Data Types

By Robert John Stevens, CEO of WriteExpress Corporation

There are four main data types: character, text, number of date.

The six character data types are:

1. text (deprecated in SQL Server 2010)

2. ntext (deprecated in SQL Server 2010)

3. char: fixed length

4. nchar: fixed length, Unicode

5. varchar: variable length

6. nvarchar: variable length, Unicode

Tips on SQL Server Data Types

• Unicode takes up two bytes per character. Maximum size allowed is 4000 characters—nvchar(4000)

• Non Unicode maximum size allowed is 8000 characters—varchar(8000)

• Fixed-length: you must predict the maximum size

• 2 GB max—nvarchar(max)

Examples of Fixed-Length SQL Server Character Data Types

• char(20): Reserves 20 bytes, padded with spaces

• nchar(20): Reserves 40 bytes, padded with spaces

Examples of Multi-Length SQL Server Character Data Types

• nvchar(20): Reserves up to 40 bytes as needed

SQL Server Number Data Types

• int: -2 billion to 2 billion

• smallint: -32,768 to 32,767

• tinyint: 0 to 255

• bigint: +/- 2^63

• money: +/- 922,337,203,685,477.5808 (notice you get four digits after the decimal point)

• smallmoney: +/- 214,748.3648 (notice you get four digits after the decimal point)

• decimal: fixed precision and scale (p,s). You define the p and s. For example: decimal(7,2) means the max value is 9999999.99

• numeric: (same as decimal)

• float(n)

• real

SQL Server Date Data Types

• date: stores a date

• time: stores a time

• datetime (deprecated): stores a date and time

• datetime2: Recommended to store both dates and times. It is more accurate and doesn't take up as much space.

• datetimeoffset: If you are working with international time zones and need to store your time offseted from universal time code

• smalldatetime: If you need less precision

Other Data Types

• binary: fixed length binary field

• varbinary: variable fixed length binary field up to 2 GB

• bit: TRUE or FALSE

• xml: raw XML data (SQL Server can understand and parse the XML data if you use this type)

• uniqueidentifier: Use this for 16-digit GUIDs

• user defined

See also: Data Types (Transact-SQL)