DATA TYPES

The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

SQL Data Types

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

SQL Server Data Types

String data types:

Data type

Description

Max size

Storage

char(n)

Fixed width character string

8,000 characters

Defined width

varchar(n)

Variable width character string

8,000 characters

2 bytes + number of chars

text

Variable width character string

2GB of text data

4 bytes + number of chars

Number data types:

Data type

Description

Storage

tinyint

Allows whole numbers from 0 to 255

1 byte

smallint

Allows whole numbers between -32,768 and 32,767

2 bytes

int

Allows whole numbers between -2,147,483,648 and 2,147,483,647

4 bytes

bigint

Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

8 bytes

decimal(p,s)

Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes

numeric(p,s)

Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes

float(n)

Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

4 or 8 bytes

real

Floating precision number data from -3.40E + 38 to 3.40E + 38

4 bytes

Date data types:

Data type

Description

Storage

datetime

From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds

8 bytes

datetime2

From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds

6-8 bytes

date

Store a date only. From January 1, 0001 to December 31, 9999

3 bytes

time

Store a time only to an accuracy of 100 nanoseconds

3-5 bytes

timestamp

Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Last updated