The SQL Server data types determine the kind and range of data that a column in a table can store (or a local variable, parameter, etc.). In this article, we will give a brief introduction to SQL Server data types, sizes & ranges. In subsequent tutorials, we look at each of these data types in more detail.
SQL Server provides several data types. We can divide them into five types.
- String Data Types
- Unicode string data types
- Exact Numeric Data Types
- Approximate Numeric Data Types
- Date/Time Data Types
- Binary Strings
- Other Data Types
Table of Contents
String Data Types
The string data types allow us to store strings of one or more characters. You can store strings, numbers, symbols & special characters, etc. They allow us to store either fixed-length strings (char/nchar) or variable-length strings (varchar/nvarchar).
String Data Types
Char & varchar are the string or character data types in SQL Server. We use them to store the string data which includes letters. numbers, symbols, special characters, etc. Char is a fixed width data type while Varchar is a variable-length Data Type.
| Data Type | Lower limit | Upper limit | Storage |
|---|---|---|---|
| char | 0 bytes | 8000 bytes | n bytes |
| varchar | 0 bytes | 8000 bytes | size of the string in bytes + 2 bytes |
| varchar(max) | 0 bytes | 2^31 (2GB) bytes | size of the string in bytes + 2 bytes |
The text data type will be eventually discarded by SQL Server, Hence not listed above. Use varchar(max) instead.
Unicode String Data Types
nchar & nvarchar (nvarchar(max)) are the Unicode string data types in SQL Server. They are similar to char & varchar but stores the strings in Unicode encoding. nchar is a fixed width data type while nvarchar is a variable-length dataType.
| Data Type | Lower limit | Upper limit | Storage |
|---|---|---|---|
| nchar | 0 bytes | 8000 bytes | 2 * n bytes |
| nvarchar | 0 bytes | 8000 bytes | 2* n bytes + 2 bytes |
| ntext | 0 bytes | 2,147,483,647 bytes | 2*n bytes |
Numeric Data Types
The number data types used for storing numeric data types. You can use these data types in arithmetic operations.
Exact Numeric Data Types
Exact numeric data types store exact numbers such as integer, decimal, or monetary amount
Integer numbers are whole numbers. They do not contain any decimal places. The sql server data types have five integer data types. They are bit, tinyint, smallint, int & bigint.
The smallmoney & money are used to represent monetary or currency values. They store data up to four decimal places.
The decimal and numeric data types store numbers that have fixed precision and scale. Note that decimal and numeric are synonyms.
| Data Type | Range from | Range to | Memory |
|---|---|---|---|
| bit | 0 | 1 | 1 byte If there are other bit columns in table, they are also merged in 1 byte (upto 8) |
| tinyint | 0 | 255 | 1 byte |
| smallint | −2^15 (−32,767) | 2^15 (−32,768) | 2 bytes |
| int | −2^31 (−2,147, 483,648) | 2^31−1 (−2,147, 483,647) | 4 bytes |
| bigint | −2^63 (−9,223,372, 036,854,775,808) | 2^63−1 (−9,223,372, 036,854,775,807) | 8 bytes |
| smallmoney | −214,478.3648 | +214,478.3647 | 4 bytes |
| money | −922,337, 203, 685,477.5808 | +922,337, 203, 685,477.5807 | 8 bytes |
| decimal | −10^38+1 | 10^381−1 | 5 to 17 bytes |
| numeric | −10^38+1 | 10^381−1 | 5 to 17 bytes |
Approximate Numeric Data Types
Float & Real Data Types in SQL Server uses the floating-point number format. Real is a Single Precision Floating Point number, while Float is a Double Precision Floating Point number. The Floating point numbers can store very large or very small numbers than decimal numbers. But this trade-off comes at the cost of precision
| Data Type | Range from | Range to | Value of n | size | Precision |
|---|---|---|---|---|---|
| float | −1.79E+308 | 1.79E+308 | 1-24 | 4 bytes | 7 Digit |
| 25-53 | 8 bytes | 15 digits | |||
| real | −3.40E+38 | 3.40E+38 | 4 bytes | 15 Digit | |
Date/Time Data Types
SQL Server supports several types to handle date and time.
The Date Data Type stores only date without time.
Use Time data type to store the only time of a day, without date & time zone and using 24 hours format
The DateTime2 Data Type stores both date & time together. The time part stores the fractional seconds Up to 7 decimal places. No of Decimal places is optional (Precision) and you can specify it while defining the DateTime2.
DateTimeOffset data type stores the date & time along with the Time Zone Offset. It is similar to DateTime2
The DateTime & SmallDateTime in SQL Server are the data types that store both date & time together. The time is based on the 24 hours clock.
| Data Type | Lower Range | Upper Range | size | Accuracy |
|---|---|---|---|---|
| date | 0001-01-01 | 9999-12-31 | 3 bytes, fixed | 1 day |
| time | 00:00:00.0000000 | 23:59:59.9999999 | 5 bytes | 100 nanoseconds |
| datetime2 | 0001-01-01 | 9999-12-31 | 6 bytes | 100 nanoseconds |
| datetimeoffset | 0001-01-01 | 9999-12-31 | 10 bytes | 100 nanoseconds |
| datetime | 1753-01-01 | 9999-12-31 | 8 bytes | Rounded to increments of .000, .003, .007 |
| smalldatetime | 1900-01-01 | 2079-06-06 | 4 bytes, fixed | 1 minute |
Binary Strings
Binary, Varbinary & varbinary(max) are the binary string data types in SQL Server. These data types are used to store raw binary data up to a length of (32K – 1) bytes
| Data Type | 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 |
Other Data Types
Apart from the above, there are few other data types that SQL Server provides. They are listed below
| Data type | Description |
|---|---|
| cursor | The cursor is the data type we use to store the reference to a set of data that we retrieve using a SELECT statement. We can use it to read one row at a time |
| rowversion | The rowversion is a special data type, which we can use as a mechanism for version-stamping table rows. The SQL Server automatically generates the values for this in the database, when we insert a new row. |
| hierarchyid | The hierarchyid data type is a variable-length, system data type. Use hierarchyid to represent the position in a hierarchy. |
| uniqueidentifier | The data type to store the 16-byte GUID column |
| sql_variant | A data type that stores values of various SQL Server-supported data types. |
| xml | xml Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. |
| Spatial Geometry Types | The data type, geometry represents data in a Euclidean (flat) coordinate system. |
| Spatial Geography Types | The geography spatial data type, geography represents data in a round-earth coordinate system |
| table | The table data type is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. |
References
Read More
