SQL - SQL Server Data Types |
SQL Server Data Types |
SQL Server has 6 main Data Types;
- Character strings:
- Unicode Strings:
- Binary Types:
- Numeric Types:
- Date Types:
- Other Data Types:
Character Strings: |
The example below specifies a maximum number size of 50 characters for the data type char()
Example: char(50)
| Data Type | Use and Description | Storage Size |
| char(size) | Fixed-length character string. | Maximum 8,000 characters |
| varchar(size) | Variable-length character string. | Maximum 8,000 characters |
| varchar(max) | Variable-length character string. | Maximum 1,073,741,824 characters |
| text | Variable-length character string. | Maximum 2GB of text data. (max 2,147,483,647 characters) |
SQL Server Unicode Text: |
| Data Type | Use and Description | Storage Size |
| nchar(size) | Fixed-length Unicode data. Character String Value | Maximum 4,000 characters |
| nvarchar(size) | Variable-length Unicode data. Character String Value | Maximum 4,000 characters |
| nvarchar(max) | Variable-length Unicode data. Character String Value | Maximum 536,870,912 characters |
| ntext | Variable-length Unicode data. Character String Value | Maximum 2GB of text data |
SQL Server Binary Types: |
| Data Type | Use and Description | Storage Size |
| bit | Integer data value. Allows 0, 1, or NULL | Either 1, 0 or NULL |
| binary(size) | Fixed-length binary data. | Maximum 8,000 bytes |
| varbinary(size) | Variable-length binary data. | Maximum 8,000 bytes |
| varbinary(max) | Variable-length binary data. | Maximum 2GB |
| image | Variable-length binary data. | Maximum 2GB |
SQL Server Numeric Types: |
| Data Type | Use and Description | Storage Size |
| tinyint | Allows whole numbers from 0 to 255 | 1 byte |
| smallint | Allows whole numbers between -32,768 and 32,767 | 2 bytes |
| int | Allows whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
| bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 | 8 bytes |
| decimal(digits,n) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The ‘digit’ parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). ‘n’ must be a value from 1 to 38. Default is 18. The ‘n’ parameter indicates the maximum number of digits stored to the right of the decimal point. ‘n’ must be a value from 0 to ‘digits’. Default value is 0 |
5-17 bytes |
| numeric(digits,size) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The ‘digits’ parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). ‘digits’ must be a value from 1 to 38. Default is 18. The ‘n’ parameter indicates the maximum number of digits stored to the right of the decimal point. ‘n’ must be a value from 0 to ‘digits’. Default value is 0 |
5-17 bytes |
| smallmoney | Monetary data from -214,748.3648 to 214,748.3647 | 4 bytes |
| money | Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
| float(n) | Floating precision number data from -1.79E + 308 to 1.79E + 308. The ‘n’ parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of ‘n’ is 53. |
4 or 8 bytes |
| real | Floating precision number data from -3.40E + 38 to 3.40E + 38 | 4 bytes |
SQL Server Date Types: |
| Data Type | Use and Description | Storage Size |
| datetime | From January 1, 1753 to December 31, 9999 Accurate to 3.33 milliseconds | 8 bytes |
| datetime2 | From January 1, 0001 to December 31, 9999 Accurate to 100 nanoseconds | 6-8 bytes |
| smalldatetime | From January 1, 1900 to June 6, 2079 Accurate to 1 minute | 4 bytes |
| date | Store a date only. From January 1, 0001 to December 31, 9999 | 3 bytes |
| time | Store a time only to an accuracy of 100 nanoseconds | 3-5 bytes |
| datetimeoffset | The same as datetime2 with the addition of a time zone offset | 8-10 bytes |
| timestamp | Stores a unique number (updates when row is updated or modified) The timestamp value is based upon an internal clock and does not correspond to real time. |
SQL Server Other Data Types: |
| Data Type | Use and Description | Storage Size |
| sql_variant | Stores up to of data of various data types, except text, ntext, and timestamp | 8,000 bytes Maximum |
| uniqueidentifier | Stores a globally unique identifier (GUID) | |
| xml | Stores XML formatted data. | Maximum 2GB |
| cursor | Stores a reference to a cursor used for database operations | |
| table | Stores a result-set for later processing |
Related SQL Sample Code:
|
|