An Overview of SQL Server Data Types (Transact-SQL)
Summary: In this article, you will learn SQL Server Data Types such as various data type including character string, unicode character string, numeric, binary string, date & time, and other data types.
SQL Server Data Tpes Introduction
The data type of a column in SQL server defines that what value the column will hold such as integer, binary, character, money, date and time, and so on.
In SQL Server, a column, parameter and variable holds a value that related with a type, or also called as a data type. Furthermore, A data type is an attribute which specified the type of data that these objects can store. It can be store value such as an integer, monetary, character string, date and time, and so on.
SQL Server provides a list of data types that define all types of data that you can use e.g., declaring a variable or defining a column.
Categories of SQL Server Data Types
-
There are the following SQL data types categories, which SQL Server supports:
- Exact numeric: int, decimal, numeric, bit, tinyint, smallint, bigint, money and smallmoney
- Approximate numeric: float and real
- Date and time: date, time, smalldatetime, DateTime, datetime2, and datetimeoffset.
- Character strings: text, char and varchar
- Unicode character strings: Ntext, Nchar and Nvarchar
- Binary strings: image binary and varbinary
- Other data types: table, XML, Cursor, rowversion, hierarchyid, sql_variant, uniqueidentifier, geography and Spatial
The following given below Diagram illustrates the SQL Server data types system:
Character Strings Data Types
Character strings data types allowed us to store either variable-length data (varchar) or fixed-length (char). The text data type can store non-Unicode data in the code page of the server.
Data Types | Lower Limit | Upper Limit | Memory |
---|---|---|---|
char | 0 chars | 8000 chars | n bytes |
varchar | 0 chars | 8000 chars | n bytes + 2 bytes |
varchar (max) | 0 chars | 2^31 chars | n bytes + 2 bytes |
text | 0 chars | 2,147,483,647 chars | n bytes + 4 bytes |
Unicode Character String Data Types
Unicode character string data types store either variable-length (nvarchar) or fixed-length (nchar) Unicode character data.
Data Types | Lower Limit | Upper Limit | Memory |
---|---|---|---|
nchar | 0 chars | 4000 chars | 2 times n bytes |
nvarchar | 0 chars | 4000 chars | 2 times n bytes + 2 bytes |
ntext | 0 chars | 1,073,741,823 char | 2 times the string length |
Exact Numeric Data Types
Exact numeric data types store exact numbers like decimal, integer, bigint, bit or monetary amount and so on.
- The bit store one among three(3) values 0, 1, and NULL
- The integer, smallint, bigint, and tinyint data types store integer data.
- The decimal and numeric data types store numbers that have fixed scale and precision . Note that numeric and decimal are synonyms.
- The money and smallmoney data type can store only currency values.
There are the following table illustrates the characteristics of the exact numeric data types:
Data Types | Lower Limit | Upper Limit | Memory |
---|---|---|---|
int | −2^31 (−2,147, 483,648) | 2^31−1 (−2,147, 483,647) | 4 bytes |
smallint | −2^15 (−32,767) | 2^15 (−32,768) | 2 bytes |
bigint | −2^63 (−9,223,372, 036,854,775,808) | 2^63−1 (−9,223,372, 036,854,775,807) | 8 bytes |
tinyint | 0 | 255 | 1 byte |
bit | 0 | 1 | 1 byte/8bit column |
numeric | −10^38+1 | 10^381−1 | 5 to 17 bytes |
decimal | −10^38+1 | 10^381−1 | 5 to 17 bytes |
money | −922,337, 203, 685,477.5808 | +922,337, 203, 685,477.5807 | 8 bytes |
smallmoney | −214,478.3648 | +214,478.3647 | 4 bytes |
Approximate Numeric Data Types
The approximate numeric data type can stores the floating point numeric data. These data type are often used in scientific calculations.
Data Types | Lower Limit | Upper Limit | Memory | Precision |
---|---|---|---|---|
float(n) | −1.79E+308 | 1.79E+308 | Depends on the value of n | 7 Digit |
real | −3.40E+38 | 3.40E+38 | 4 bytes | 15 Digit |
Binary String Data Types
The binary string data types can stores the fixed and variable length binary data.
Data Types | Lower Limit | Upper Limit | Memory |
---|---|---|---|
binary | 0 bytes | 8000 bytes | n bytes |
varbinary | 0 bytes | 8000 bytes | The actual length of data entered + 2 bytes |
image | 0 bytes | 2,147,483,647 bytes |
Date & Time Data Types
The date and time data types can store the only data and time data, and the date time offset.
Data Types | Storage Size | Accuracy | Lower Range | Upper Range |
---|---|---|---|---|
date | 3 bytes, fixed | 1 day | 0001-01-01 | 9999-12-31 |
time | 5 bytes | 100 nanoseconds | 00:00:00.0000000 | 23:59:59.9999999 |
datetime | 8 bytes | Rounded to increments of .000, .003, .007 | 1753-01-01 | 9999-12-31 |
datetime2 | 6 bytes | 100 nanoseconds | 0001-01-01 | 9999-12-31 |
smalldatetime | 4 bytes, fixed | 1 minute | 1900-01-01 | 2079-06-06 |
datetimeoffset | 10 bytes | 100 nanoseconds | 0001-01-01 | 9999-12-31 |
If you develop a new application, then you should use the date, time, datetime2 and datetimeoffset data types. as a result of these types align with the SQL Standard and more portable. furthermore, in addition, the time, datetimeoffset and datetime2 have a lot seconds precision and datetimeoffset supports time zone.
Other Data Types
Data Types | Storage Size |
---|---|
cursor | for the variables or stored procedure OUTPUT parameter that contains a reference to a cursor |
rowversion | show up automatically generated, unique binary numbers within a database. |
hierarchyid | characterize a tree position in a tree hierarchy |
uniqueidentifier | 16-byte GUID |
sql_variant | store values of other data types |
XML | To store XML data in a column, or the variable of XML type |
Spatial Geometry type | represent data in a flat coordinate system. |
Spatial Geography type | store ellipsoidal (round-earth) data, like GPS latitude and longitude coordinates. |
table | store a result set short-term for processing at a later time |
Conclusion: SQL Server Data Types
you have learned about the brief overview of SQL Server data types such as including character string, unicode character string, numeric, binary string, date & time, and other data types etc. I hope you will enjoy! this article.