SQL Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.
SQL Server offers six categories of data types for your use which are listed below −
Exact Numeric Data Types
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 Time Data 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.
Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.
Character Strings Data Types
Sr.No.
DATA TYPE & Description
1
charMaximum length of 8,000 characters.( Fixed length non-Unicode characters)
2
varcharMaximum of 8,000 characters.(Variable-length non-Unicode data).
3
varchar(max)Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only).
4
textVariable-length non-Unicode data with a maximum length of 2,147,483,647 characters.
Unicode Character Strings Data Types
Sr.No.
DATA TYPE & Description
1
ncharMaximum length of 4,000 characters.( Fixed length Unicode)
2
nvarcharMaximum length of 4,000 characters.(Variable length Unicode)
3
nvarchar(max)Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)
4
ntextMaximum length of 1,073,741,823 characters. ( Variable length Unicode )
Binary Data Types
Sr.No.
DATA TYPE & Description
1
binaryMaximum length of 8,000 bytes(Fixed-length binary data )
2
varbinaryMaximum length of 8,000 bytes.(Variable length binary data)
3
varbinary(max)Maximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data)
4
imageMaximum length of 2,147,483,647 bytes. ( Variable length Binary Data)
Misc Data Types
Sr.No.
DATA TYPE & Description
1
sql_variantStores values of various SQL Server-supported data types, except text, ntext, and timestamp.
2
timestampStores a database-wide unique number that gets updated every time a row gets updated
3
uniqueidentifierStores a globally unique identifier (GUID)
4
xmlStores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).