There are different types of data based on different characteristics. When you go to create a table, you need to tell the database what types of information will be stored in each field of the table. Each column or field in a database table needs a field name and a data. The different types of fields store different types of data. These different kinds of data are classified into data types. So, a data type is an attribute that denotes the type of data. SQL Server supports a number of data types in several categories that defines almost all the types of data. They are as follows:
- Numeric data types
- Date and time data types
- String (character and byte) data types
- Other data types
These different types of data type with explanations and examples are listed in the table that follows.
String (character and byte) data types :
| Data type | Size | Description | Examples |
|---|---|---|---|
| char(n) | 8,000 characters. | Store character based data. Non-Unicode fixed length data type. n defines the string size in bytes | If the field has two characters then use char (2) char takes up 1 byte per character. |
| varchar(n) | 8,000 characters. | Non-Unicode variable length data type. n defines the string size in bytes. Maximum storage 2^31-1 bytes (2 GB). Storage is measured: 2 bytes + number of chars. | If the field is set as varchar (100) data type = ‘DAD’, then it would take up 3 bytes (for D, A, and D) plus 2 bytes. |
| varchar(max) | 1,073,741,824 characters | Non-Unicode Variable length data type. Max indicates that the maximum storage size is 2^31-1 bytes. If exceed Varchar (max), column value is stored in a separate LOB data pages. Storage is measured: 2 bytes + number of chars. | varchar(MAX) data does not compress. varchar (MAX) has some ambiguity, if the size of the cell is < 8000 chars, it will be treated as Row data. |
| text | 2GB of text data or 2147483647 characters | Non-Unicode large Variable Length character data type. Storage is measured: 4 bytes + number of chars | Blog comments, wiki pages, code source etc. field that allows larger text to be stored. |
| nchar | 4,000 characters. | Unicode character string data with fixed length. n specifies the string length that ranges from 1 to 4,000. Storage is two times n bytes. Defined width x 2 | nchar(55) returns the Unicode character based on the number code 55. |
| nvarchar | 4,000 characters. | Unicode variable string data type. Storage supports two-bytes per characters. | This data type stores unicode support character data that is a mixture of English and non-English symbols. |
| nvarchar(max) | 536,870,912 characters. | Variable-length unicode string data. Here max is the maximum storage size in bytes which is 2^31-1 bytes (2 GB). | nvarchar (100) allows 100 characters. |
| text | 2GB of text data or 2147483647 characters. | Non-Unicode large Variable Length character data type. Storage is measured: 4 bytes + number of chars. | Blog comments, wiki pages, code source etc. field that allows larger text to be stored. |
| nchar | 4,000 characters. | Unicode character string data with fixed length. n specifies the string length that ranges from 1 to 4,000. Storage is two times n bytes. Defined width x 2. | nchar(55) returns the Unicode character based on the number code 55. |
| nvarchar | 4,000 characters. | Unicode variable string data type. Storage supports two-bytes per characters. | This data type stores unicode support character data that is a mixture of English and non-English symbols. |
| nvarchar(max) | 536,870,912 characters. | Variable-length unicode string data. Here max is the maximum storage size in bytes which is 2^31-1 bytes (2 GB). | nvarchar (100) allows 100 characters. |
| ntext | 2GB of text data. | Variable width Unicode string | |
| binary(n) | 8,000 bytes | Fixed-length binary data. Store n bytes of fixed-size binary data. Maximum of 8,000 bytes. | |
| varbinary | 8,000 bytes. | Variable-length binary data. | |
| varbinary (max) | 2GB | Variable-length binary data. Max indicates that the maximum storage size is 2^31-1 bytes. The column data entries exceed 8,000 bytes. |
Numeric data types:
| Data type | Size | Description | Example |
| bit | It can take a value of 0, 1, or NULL. | An integer data type. If a table has 7 or fewer bit columns, SQL Server stores them as 1 byte.and if 16 bit columns then store them as 2 bytes | . |
tinyint | 0 to 255 | Integer types of data. Storage 1 byte. | |
smallint | From -215 (-32,768) to 215-1 (32,767) | Integer types data. Storage 2 byte. | |
int | From numbers between -231 (-2,147,483,648) to 231-1 (2,147,483,647) | Integer types data. Storage 4 bytes. | |
| bigint | From numbers between -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807) | Integer type data. Storage 8 Bytes. | |
| decimal(p,s) | Number from -10^38 +1 to 10^38 to 10^38 –1. | Fixed precision and scale numbers. p is the precision and s is the scale. The precision has a range from 1 to 38. The default precision is 38. By default, the scale is zero. | |
| numeric(p,s) | Numbers from -10^38 +1 to 10^38 –1 | ||
| smallmoney | From -214,748.3648 to 214,748.3647 | Monetary data Storage is 4 bytes | |
| money | From -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | Monetary data Storage is 8 bytes. | |
| float(n) | From -1.79E + 308 to 1.79E + 308. | Floating precision number data. Storage is 4 or 8 bytes. | |
| real | From -3.40E + 38 to 3.40E + 38. | Floating precision number data. Storage is 4 bytes. |
Date and Time data types:
| Data Type | Size range | Explanation | Example |
| datetime | From January 1, 1753 to December 31, 9999 | The storage is 8 bytes. | |
| datetime2 | From January 1, 0001 to December 31, 9999 | Storage is 6-8 bytes. | |
| smalldatetime | From January 1, 1900 to June 6, 2079 | Storage is 4 bytes | |
| date | From January 1, 0001 to December 31, 9999 | Store a date only. Storage is 3 bytes. | |
| time | 100 nanoseconds | Store a time only. Storage is 3-5 bytes. | |
| datetimeoffset | The same as datetime2. Time zone offset added. | Storage is 8-10 bytes. | |
| timestamp | Stores a unique number that gets updated. | The timestamp value is based upon an internal clock and does not correspond to real time. |
Other data types (Miscellaneous):
| Data Type | Size range | Explanation | Example |
| sql_variant | 8,000 bytes | Stores data of various data types, except text, n text, and timestamp. | |
| uniqueidentifier | 16 byte binary | Stores a globally unique identifier (GUID) | |
| xml | Maximum 2GB | Stores XML formatted data. | |
| cursor | Stores a reference to a cursor used for database operations. | ||
| table | Stores a result-set for later processing. | ||
| BLOB | Maximum 2 gigabytes | For binary large objects. | |
| CLOB | Hold up to 2GB | Stores variable-length character data (character large object). | |
| JSON | JSON is not a native data type. It is used for storing JSON data. |
