SQL Server information type is a trait that determines kinds of information of any item. Every section, variable and articulation has related information type in SQL Server. These information types can be utilized while making tables. You can pick a specific information type for a table segment dependent on your prerequisite.
SQL Server offers seven classes including other classification of information types for use.
Exact Numeric Types
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 |
Numeric and decimal are Fixed accuracy and scale information types and are practically same.
Approximate Numeric Types
Type | From | To |
---|---|---|
Float | -1.79E + 308 | 1.79E + 308 |
Real | -3.40E + 38 | 3.40E + 38 |
Date and Time Types
Type | From | To |
---|---|---|
datetime(3.33 milliseconds accuracy) |
Jan 1, 1753 | Dec 31, 9999 |
smalldatetime(1 minute accuracy) |
Jan 1, 1900 | Jun 6, 2079 |
date(1 day accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
datetimeoffset(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
datetime2(100 nanoseconds accuracy. Introduced inSQL Server 2008) |
Jan 1, 0001 | Dec 31, 9999 |
time(100 nanoseconds accuracy. Introduced inSQL Server 2008) |
00:00:00.0000000 | 23:59:59.9999999 |
Character Strings
Sr.No | Type & Description |
---|---|
1 |
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
2 |
varchar Variable-length non-Unicode data with a maximum of 8,000 characters. |
3 |
Varchar (max) Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005). |
4 |
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters |
Unicode Character Strings
Sr.No | Type & Description |
---|---|
1 |
nchar Fixed-length Unicode data with a maximum length of 4,000 characters. |
2 |
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters. |
3 |
Nvarchar (max) Variable-length Unicode data with a maximum length of 230characters (Introduced in SQL Server 2005). |
4 |
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters. |
Binary Strings
Sr.No | Type & Description |
---|---|
1 |
binary Fixed-length binary data with a maximum length of 8,000 bytes. |
2 |
varbinary Variable-length binary data with a maximum length of 8,000 bytes. |
3 |
varbinary(max) Variable-length binary data with a maximum length of 231bytes (Introduced in SQL Server 2005). |
4 |
image Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
Other Data Types
- sql_variant − Stores estimations of different SQL Server-upheld information types, aside from text, ntext, and timestamp.
- timestamp − Stores a data set wide one of a kind number that gets refreshed each time a line gets refreshed.
- uniqueidentifier − Stores a universally extraordinary identifier (GUID).
- xml − Stores XML information. You can store XML occurrences in a segment or a variable (Introduced in SQL Server 2005).
- cursor − A reference to a cursor.
- table − Stores an outcome set for later handling.
- hierarchyid − A variable length, framework information type used to speak to position in a chain of importance (Introduced in SQL Server 2008).