T Sql 简明教程
T-SQL - Data Types
SQL Server 数据类型是一种属性，它指定了任何对象的 data 类型。每列，变量和表达式在 SQL Server 中有相关的 data 类型。在创建表时可使用这些数据类型。你可针对你的需求为表格栏选择特定的数据类型。
SQL Server data type is an attribute that specifies types of data of any object. Each column, variable and expression has related data type in SQL Server. These data types can be used while creating tables. You can choose a particular data type for a table column based on your requirement.
SQL Server 提供了七个类别包括其他类别的 data 类型以供使用。
SQL Server offers seven categories including other category of data types for use.
Exact Numeric Types
Type |
From |
To |
bigint |
-9,223,372,036,854,775,808 |
9,223,372,036,854,775,807 |
int |
-2,147,483,648 |
2,147,483,647 |
smallint |
-32,768 |
32,767 |
tinyint |
0 |
255 |
bit |
0 |
1 |
decimal |
-10^38 +1 |
10^38 –1 |
numeric |
-10^38 +1 |
10^38 –1 |
money |
-922,337,203,685,477.5808 |
+922,337,203,685,477.5807 |
smallmoney |
-214,748.3648 |
+214,748.3647 |
数字和十进制是固定精度和刻度 data 类型，以及在功能上是相等的。
Numeric and decimal are Fixed precision and scale data types and are functionally equivalent.
Date and Time Types
Type |
From |
To |
datetime(3.33 milliseconds accuracy) |
Jan 1, 1753 |
Dec 31, 9999 |
smalldatetime(1 minute accuracy) |
Jan 1, 1900 |
Jun 6, 2079 |
date(1 day accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 |
Dec 31, 9999 |
datetimeoffset(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 |
Dec 31, 9999 |
datetime2(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
Jan 1, 0001 |
Dec 31, 9999 |
time(100 nanoseconds accuracy. Introduced in SQL Server 2008) |
00:00:00.0000000 |
23:59:59.9999999 |
Character Strings
Sr.No |
Type & Description |
1 |
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
2 |
varchar Variable-length non-Unicode data with a maximum of 8,000 characters. |
3 |
Varchar (max) Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005). |
4 |
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters |
Unicode Character Strings
Sr.No |
Type & Description |
1 |
nchar Fixed-length Unicode data with a maximum length of 4,000 characters. |
2 |
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters. |
3 |
Nvarchar (max) Variable-length Unicode data with a maximum length of 230 characters (Introduced in SQL Server 2005). |
4 |
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters. |
Binary Strings
Sr.No |
Type & Description |
1 |
binary Fixed-length binary data with a maximum length of 8,000 bytes. |
2 |
varbinary Variable-length binary data with a maximum length of 8,000 bytes. |
3 |
varbinary(max) Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005). |
4 |
image Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
Other Data Types
-
sql_variant − Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
-
timestamp − Stores a database-wide unique number that gets updated every time a row gets updated.
-
uniqueidentifier − Stores a globally unique identifier (GUID).
-
xml − Stores XML data. You can store XML instances in a column or a variable (Introduced in SQL Server 2005).
-
cursor − A reference to a cursor.
-
table − Stores a result set for later processing.
-
hierarchyid − A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).