Data types in sql server

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

Saving disk space is not the only benefit of properly selected data types for SQL Server columns. In addition to disk space benefits, the right data types reduce backup times, improve SQL Server performance, and enhance the execution of queries and joins. There are diffrent type of data types in sql these are given below......  

Exact Numeric Data 

DATA TYPE	FROM	TO
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 -1
numeric	-10^38 +1	10^38 -1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

Approximate Numeric Data Types:

DATA TYPE	FROM	TO
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

Date and smalldatetime Types

DATA TYPE	FROM	TO
datetime	Jan 1, 1753	Dec 31, 9999
smalldatetime	Jan 1, 1900	Jun 6, 2079
date	Stores a date like June 30, 1991
time	Stores a time of day like 12:30 P.M.

Character Strings

DATA TYPE	FROM	TO
char	char	Fixed length non-Unicode characters with a maximum length of 8,000 characters.
varchar	varchar	Variable-length non-Unicode data with maximum of 8,000 characters.
varchar(max)	varchar(max)	Variable-length non-Unicode data with Maximum length of 231 characters,  (SQL Server 2005 only).
text	text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings Data 

DATA TYPE	Description
nchar	Fixed length Unicode with maximum length of 4,000 characters.
nvarchar	Maximum length of 4,000 characters.(Variable length Unicode)
nvarchar(max)	Maximum length of 231 characters (SQL Server 2005 only).( Variable length Unicode)
ntext	Maximum length of 1,073,741,823 characters. ( Variable length Unicode )

Binary Data Types

DATA TYPE	Description
binary	Fixed-length binary data with maximum length of 8,000 bytes
varbinary	Variable length binary data with maximum length of 8,000 bytes.
varbinary(max)	Maximum length of 231 bytes (SQL Server 2005 only). ( Variable length Binary data)
image	Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)

Others Data Types

1.                   sql_variant-> Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.

2.                   timestamp->  Stores a database-wide unique number that gets updated every time a row gets updated.

3.                   uniqueidentifier-> Stores a globally unique identifier (GUID).

4.                   xml-> Stores XML data. You can store xml instances in a column or a variable (Introduced in SQL Server 2005).

5.                   cursor-> A reference to a cursor.

6.                   table-> Stores a result set for later processing.

7.                   hierarchyid-> A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).