SQL Server - DATA TYPES

---

SQL Server - DATA TYPES

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.

Below are the Data Type categories in SQL Server

Categories
Exact numerics
Approximate numerics
Date and time
Character strings
Unicode character strings
Binary strings
Other data types

Exact numerics

Datatype	Range	Storage
bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 bytes
int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 bytes
smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 bytes
tinyint	0 to 255	1 byte
bit	0-1	-
decimal	-10^38 +1 to 10^38 -1	5-17 bytes
numeric	-10^38 +1 to 10^38 -1	5-17 bytes
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. Informatica only supports two decimals, not four.)	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

Approximate numerics

Datatype	Description	Range
float	Floating point Numeric data.n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If nis specified, it must be a value between 1 and 53. The default value of n is 53. If n valueIf 1<=n<=24, n is treated as 24, precision will be 7 digits and storage size will be 4 bytes.If 25<=n<=53, n is treated as 53, precision will be 15 digits and storage size will be 8 bytes	-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
real	REAL is FLOAT(24)4 Bytes	-3.40E + 38 to -1.18E -38, 0 and 1.18E -38 to 3.40E +38

Date and Time

Datatype	Description	Storage size	Accuracy	Lower range	Upper range
dateTime	Used for specifying a date and time from January 1, 1753 to December 31, 9999.It has an accuracy of 3.33 milliseconds	8 bytes	Rounded to increments of .000, .003, .007	1753-01-01	9999-12-31
smalldatetime	Used for specifying a date and time from January 1, 0001 to December 31, 9999. It has an accuracy of 100 nanoseconds	4 bytes, fixed	1 minute	1900-01-01	2079-06-06
date	Used to store only date from January 1, 0001 to December 31, 9999	3 bytes, fixed	1day	0001-01-01	9999-12-31
time	Used for storing only time only values with an accuracy of 100	5 bytes	100 nanoseconds	00:00:00.0000000	23:59:59.9999999
datetime2	Used for specifying a date and time from January 1, 0001 to December 31, 9999	6 bytes	100 nanoseconds	0001-01-01	9999-12-31
datatimeoffset	Similar to datatime but has a time zone offset	10 bytes	100 nanoseconds	0001-01-01	9999-12-31

Character strings

Character string allows the user to define the data type of character which can be of fixed and variable length.

Datatype	Description	Lower limit	Upper limit	Memory
char	It is a character string with a fixed width. It stores a maximum of 8,000 characters	0 chars	8000 chars	n bytes
varchar	This is a character string with variable width	0 chars	8000 chars	n bytes + 2 bytes
varchar(max)	This is a character string with a variable width. It stores a maximum of 1,073,741,824 characters	0 chars	1,073,741,824 characters	n bytes + 2 bytes
text	This is a character string with a variable width. It stores a maximum 2GB of text data	0 chars	2,147,483,647 chars	n bytes + 4 bytes

Unicode characters

Datatype	Description	Lower limit	Upper limit	Memory
nchar	It is a Unicode string of fixed width	0 chars	4000 chars	2 times n bytes
nvarchar	It is a unicode string of variable width	0 chars	4000 chars	2 times n bytes + 2 bytes
ntext	It is a unicode string of variable width	0 chars	1,073,741,823 char	2 times the string length

Binary string

Binary string contains a binary string of fixed and variable length.

Datatype	Description	Lower limit	Upper limit	Memory
binary	It is a fixed width binary string. It stores a maximum of 8,000 bytes	0 bytes	8000 bytes	N bytes
varbinary	This is a binary string of variable width. It stores a maximum of 8,000 bytes	0 bytes	8000 bytes	The actual length of data entered + 2 bytes
image	This is a binary string of variable width. It stores a maximum of 2GB	0 bytes	2,147,483,647 bytes	-

Other Datatypes

Datatype	Description
Cursor	Its output is a column of sp_cursor_list and sp_describe_cursor. It returns the name of the cursor variable
Row version	It version stamps table rows
Hierarchyid	This datatype represents a position in the hierarchy
Uniqueidentifier	Conversion from a character expression
Sql_variant	It stores values of SQL server supported Datatypes
XML	It stores XML data in a column
Spatial Geometry type	It represents data in a flat coordinate system
Spatial Geography type	It represents data in the round-earth coordinate system
table	It stores a result set for later processing