Column Data-types in SQL Server

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

In this post I will describe SQL Server data-types along with their size (in bytes) and the domain values that can be taken by the data types.

Following data-types can be used for a column.

Data type	Size [bytes]	Description
Exact Numerics
bit	1	An integer data type that can take a value of 1, 0, or NULL. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
tinyint	1	Integer data type that can store 0 to 2^8-1 (255)
smallint	2	Integer data type that can store -2^15 (-32,768) to 2^15-1 (32,767)
int	4	Integer data type that can store -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigint	8	Integer data type that can store -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal	5-17	Numeric data types that have fixed precision and scale.
numeric	5-17	Numeric data types that have fixed precision and scale.
smallmoney	4	Currency that can store - 214,748.3648 to 214,748.3647
money	8	Currency that can store -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Approxmate Numerics
float	4-8	Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Float cans store - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
real	4	Real can store - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Date and Time
date	3	stores DATE in 'YYYY-MM-DD'. Range 0001-01-01 through 9999-12-31
smalldatetime	4	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1900-01-01 00:00:00 through 2079-06-06 23:59:59
datetime	8	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1753-01-01 00:00:00 through 9999-12-31 23:59:59
time	5	Stores only time like 'hh:mm:ss.nnnnnnn'. Range 00:00:00.0000000 through 23:59:59.9999999
Character Strings
char	n	CHAR(n). Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8000.
varchar	n	VARCHAR(n | max). Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
text		Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). Storage size, in bytes is the string length entered.
Unicode Character Strings
nchar	2n	CHAR(n). Fixed-length, Unicode string data. n defines the string length and must be a value from 1 through 4000.
nvarchar	2n	VARCHAR(n | max). Variable-length, Unicode string data. n defines the string length and can be a value from 1 through 4000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
ntext		Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823). Storage size, in bytes, is two times the string length entered
Unicode Character Strings
binary	n	Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8000
image		Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.