SQL Server 数据类型

The following sections describe the data types supported by SQL Server.

## SQL Server Data Types
The data types supported by the Microsoft SQL Server can be grouped into three main categories: string, numeric and date/time data types.

### String Data Types
String data types are normally used to store names, addresses, descriptions or any value that contains letters and numbers including binary data, like image or audio files.

| Data type      | Description   |
|----------------|--------------|
| char(n)        | Stores fixed-length character string. Maximum length 8,000 characters.                              |
| varchar(n)     | Stores variable-length character string. Maximum length 8,000 characters.                           |
| varchar(max)   | Stores variable-length character string. Here, max indicates that the maximum storage size is 2 GB. |
| text           | Stores variable-length character string. Maximum storage size is 2 GB.                              |
| nchar          | Stores fixed-length Unicode string. Maximum length 4,000 characters.                                |
| nvarchar       | Stores variable-length Unicode string. Maximum length 4,000 characters.                             |
| nvarchar(max)  | Stores variable-length Unicode string. Here, max indicates that the maximum storage size is 2 GB.   |
| ntext          | Stores variable-length Unicode string. Maximum storage size is 2 GB.                                |
| binary(n)      | Stores fixed-length binary data. Maximum storage size is 8,000 bytes.                               |
| varbinary(n)   | Stores variable-length binary data. Maximum storage size is 8,000 bytes.                            |
| varbinary(max) | Stores variable-length binary data. Here, max indicates that the maximum storage size is 2 GB.      |
| image          | Stores variable-length binary data. Maximum storage size is 8,000 bytes.                            |

### Numeric Data Types
Numeric data types are normally used to store data like price, salary etc.

| Data type    | Description                                                                                                                         |
|--------------|-------------------------------------------------------------------------------------------------------------------------------------|
| bit          | Allows you to store a value 1, 0, or NULL.                                                                                          |
| tinyint      | Stores integer values in the range from 0 to 255.                                                                                   |
| smallint     | Stores integer values in the range from -32,768 to 32,767.                                                                          |
| int          | Stores integer values in the range from -2,147,483,648 to 2,147,483,647.                                                            |
| bigint       | Stores integer values in the range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.                                    |
| decimal(p,s) | Stores fixed precision and scale numbers. Valid values are from 10^38 +1 through 10^38 - 1. See below for more details.             |
| numeric(p,s) | The numeric datatype is functionally equivalent to decimal.                                                                         |
| smallmoney   | Allows you to store monetary or currency values accurately in the range from -214,748.3648 to 214,748.3647.                         |
| money        | Allows you to store monetary or currency values accurately in the range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. |
| float(n)     | Stores floating point numeric values. Valid values are from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308.                 |
| real         | Stores floating point numeric values. Valid values are from -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.             |

When declaring a or decimal or numeric column, the precision and scale can be specified, like decimal(p,s) or numeric(p,s), where the p or precision indicates the maximum total number of digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 to 38. The default precision is 18.

Whereas, the s or scale indicates the maximum number of digits that can be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 to p. The default scale is 0.

For example, the price decimal(6,2) column can store any value with six digits and two decimals i.e. in the range from -9999.99 to 9999.99.

### Date and Time Data Types
Date and Time data types are normally used to store data like date of birth, hiring date, date and time when a record is created or updated inside table, etc.

| Data type      | Description       |
|----------------|-------------------|
| date           | Stores a date value in the range from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999). |
| time           | Stores time of a day with the accuracy of 100 nanoseconds. Valid values are from 00:00:00.0000000 to 23:59:59.9999999. |
| datetime       | Stores a combined date and time value with an accuracy of 3.33 milliseconds. Valid date range for datetime is from 1753-01-01 (January 1, 1753) to 9999-12-31 (December 31, 9999).                           |
| datetime2      | The datetime2 is an extension of the datetime data type that has a larger date range. Valid date range for datetime2 is from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999).                    |
| smalldatetime  | Stores a combined date and time value with an accuracy of 1 minute. Valid date range for smalldatetime is from 1900-01-01 (January 1, 1900) to 2079-06-06 (June 6, 2079).                                    |
| datetimeoffset | Same as datetime2 with the addition of a time zone offset. Default format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. Valid range for time zone offset is from -14:00 to +14:00.                          |
| timestamp      | In SQL Server timestamp is the synonym for the rowversion data type which automatically generate, unique binary numbers within a database. The rowversion is generally used for version-stamping table rows. |

<div class="callout callout-info mb-3">Note: Every time that a row with a rowversion column is inserted or modified inside a table, the incremented database rowversion value is inserted in the rowversion column. A table can have only one rowversion column.</div>