English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
The following sections describe the data types supported by SQL Server.
The data types supported by Microsoft SQL Server can be divided into three main categories:Strings,NumbersandDate/TimeData types.
String data types are typically used to store names, addresses, descriptions, or any values containing letters and numbers, including binary data such as images or audio files.
| Data type | Description |
|---|---|
| char(n) | Store fixed-length strings. The maximum length is8,000 characters. |
| varchar(n) | Store strings of variable length. The maximum length is8,000 characters. |
| varchar(max) | Store strings of variable length. Here, max indicates the maximum storage size of2 GB. |
| text | Store strings of variable length. The maximum storage size is2 GB. |
| nchar | Store fixed-length Unicode strings. The maximum length is4,000 characters. |
| nvarchar | Store variable-length Unicode strings. The maximum length is4,000 characters. |
| nvarchar(max) | Store variable-length Unicode strings. Here, max indicates the maximum storage size of2 GB. |
| ntext | Store variable-length Unicode strings. The maximum storage size is2 GB. |
| binary(n) | Store fixed-length binary data. The maximum storage size is8,000 bytes. |
| varbinary(n) | Stores variable-length binary data. The maximum storage size is8,000 bytes. |
| varbinary(max) | Stores variable-length binary data. Here, max indicates the maximum storage size is2 GB. |
| image | Stores variable-length binary data. The maximum storage size is8,000 bytes. |
Numeric data types are commonly used to store data such as prices, salaries, etc.
| Data type | Description |
|---|---|
| bit | allows you to store values1、0 orNULL. |
| tinyint | Stores integer values from255and |
| between | stores integer values within the range of-32,768to32,767int |
| smallint | stores integer values within the range of-2,147,483,648to2,147,483,647int |
| bigint | stores integer values within the range of-9,223,372,036,854,775,808to9,223,372,036,854,775,807integer values. |
| decimal(p,s) | stores fixed precision and scale. Valid values are10 ^38 +1to10 |
| numeric(p,s) | The numeric data type is functionally equivalent to decimal. |
| smallmoney | allows you to store monetary values precisely or within a range of-214,748.3648to214,748.3647. |
| money | allows you to store monetary values precisely or within a range of-922,337,203,685,477.5808to922,337,203,685,477.5807. |
| float(n) | stores floating-point numbers. Valid values are-1.79E + 308to-2.23E-308、0 and2.23E-308to1.79E + 308. |
| real | stores floating-point numbers. Valid values are-3.40E + 38to-1.18E-38、0 and1.18E-38to3.40E + 38. |
When declaring a decimal or numeric column, you can specify precision and scale, such as decimal(p,s) or numeric(p,s), where p or precision represents the maximum number of digits that can be stored, including to the left and right of the decimal point. The precision must be from1to38values. The default precision is18.
whilesor scale indicates the maximum number of digits that can be stored to the right of the decimal point. Subtract this number from p to determine the maximum number of digits to the left of the decimal point. The number of decimal places must be a value between 0 and p. The default scale is 0.
For example, price decimal(6,2) column can store any value with six digits and two decimals, that is-9999.99to9999.99values.
The datetime data type is commonly used to store data such as birth date, employment date, and the date and time when a record is created or updated in a table, etc.
| Data type | Description |
|---|---|
| date | Stores date values, ranging from 0001-01-01(1month1day,1month) to 9999-12-31(9999year12month31day). |
| time | Stores time in a day with precision of100 nanoseconds. Valid values are 00:00:00.0000000 to23:59:59.9999999. |
| datetime | Stores a combination of date and time values with precision of3.33milliseconds. The valid date range datetime is from1753-01-01(1753year1month1day) to9999-12-31(9999year12month31day). |
| datetime2 | datetime2is an extension of the datetime data type, with a larger date range. datetime2The valid date range is from 0001-01-01 (January 1, 1) to9999-12-31 (December 31, 9999)。 |
| smalldatetime | with1The precision storage combination of date and time values. The valid date range of smalldatetime is from1900-01-01(1900 year1month1day) to 2079-06-06(2079year6month6day). |
| datetimeoffset | With datetime2Add the same time zone offset. The default format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-The valid range of time zone offset is-14:00 to+14:00. |
| timestamp | In SQL Server, timestamp is a synonym for the rowversion data type, which automatically generates a unique binary number in the database. rowversion is typically used for version marking table rows |
Note:Each time a row with a rowversion column is inserted or modified in the table, the database increments the rowversion value and inserts it into the rowversion column. A table can only have one rowversion column.