Sql Server Data Types
SQL Server: Data Types
The following is a list of datatypes available in SQL Server (Transact-SQL), which includes string, numeric, and date/time datatypes.
String Datatypes
The following are the String Datatypes in SQL Server (Transact-SQL):
| Data Type Syntax | Maximum Size | Explanation |
|---|---|---|
| CHAR(size) | Maximum size of 8,000 characters. | Where size is the number of characters to store. Fixed-length. Space padded on right to equal size characters. Non-Unicode data. |
| VARCHAR(size) or VARCHAR(max) | Maximum size of 8,000 or max characters. | Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Non-Unicode data. |
| TEXT | Maximum size of 2GB. | Variable-length. Non-Unicode data. |
| NCHAR(size) | Maximum size of 4,000 characters. | Fixed-length. Unicode data. |
| NVARCHAR(size) or NVARCHAR(max) | Maximum size of 4,000 or max characters. | Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Unicode data. |
| NTEXT | Maximum size of 1,073,741,823 bytes. | Variable length. Unicode data. |
| BINARY(size) | Maximum size of 8,000 characters. | Where size is the number of characters to store. Fixed-length. Space padded on right to equal size characters. Binary data. |
| VARBINARY(size) or VARBINARY(max) | Maximum size of 8,000 or max characters. | Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Non-Binary data. |
| IMAGE | Maximum size of 2GB. | Variable length . Binary data. |
Numeric Datatypes
The following are the Numeric Datatypes in SQL Server (Transact-SQL):
| Data Type Syntax | Maximum Size | Explanation |
|---|---|---|
| BIT | Integer that can be 0, 1, or NULL. | |
| TINYINT | 0 to 255 | |
| SMALLINT | -32768 to 32767 | |
| INT | -2,147,483,648 to 2,147,483,647 | |
| BIGINT | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | |
| DECIMAL(m,d) | m defaults to 18, if not specified. d defaults to 0, if not specified. | Where m is the total digits and d is the number of digits after the decimal. |
| DEC(m,d) | m defaults to 18, if not specified. d defaults to 0, if not specified. | Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DECIMAL datatype. |
| NUMERIC(m,d) | m defaults to 18, if not specified. d defaults to 0, if not specified. | Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DECIMAL datatype. |
| FLOAT(n) | Floating point number. n defaults to 53, if not specified. | Where n is the number of number of bits to store in scientific notation. |
| REAL | Equivalent to FLOAT(24) | |
| SMALLMONEY | - 214,748.3648 to 214,748.3647 | |
| MONEY | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
Date/Time Datatypes
The following are the Date/Time Datatypes in SQL Server (Transact-SQL):
| Data Type Syntax | Maximum Size | Explanation (if applicable) |
|---|---|---|
| DATE | Values range from '0001-01-01' to '9999-12-31'. | Displayed as 'YYYY-MM-DD' |
| DATETIME | Date values range from '1753-01-01 00:00:00' to '9999-12-31 23:59:59'. Time values range from '00:00:00' to '23:59:59:997' | Displayed as 'YYYY-MM-DD hh:mm:ss[.mmm]' |
| DATETIME2(fractional seconds precision) | Date values range from '0001-01-01' to '9999-12-31'. Time values range from '00:00:00' to '23:59:59:9999999'. | Displayed as 'YYYY-MM-DD hh:mm:ss[.fractional seconds]' |
| SMALLDATETIME | Date values range from '1900-01-01' to '2079-06-06'. Time values range from '00:00:00' to '23:59:59'. | Displayed as 'YYYY-MM-DD hh:mm:ss' |
| TIME | Values range from '00:00:00.0000000' to '23:59:59.9999999' | Displayed as 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' |
| DATETIMEOFFSET(fractional seconds precision) | Date values range from '0001-01-01' to '9999-12-31'. Time values range from '00:00:00' to '23:59:59:9999999'. Time zone offset range from -14:00 to +14:00. | Displayed as 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm] |
