实现SQL数据类型

This article is about many different SQL data types that we use when working with SQL Server. We will start with a quick overview and go through some stuff like categories of data types, what objects we can work with, and how to create our own custom data types.

本文介绍了在使用SQL Server时我们使用的许多不同SQL数据类型。 我们将从快速概述开始，并介绍诸如数据类型类别，可以使用的对象以及如何创建我们自己的自定义数据类型之类的内容。

SQL数据类型概述 (SQL data types overview)

To kick things off, let’s talk about what is a data type. If I had to define it, I’d say that data type determines the kind, size, and range of data that can be stored in an object. So, this brings us to a question of objects that have data types:

首先，让我们谈谈什么是数据类型。 如果必须定义它，我会说数据类型确定可以存储在对象中的数据的类型，大小和范围。 因此，这使我们想到了具有数据类型的对象的问题：

• Columns
  列
• Variables
  变数
• Expressions
  表达方式
• Parameters
  参量

These four SQL data types of objects are of the highest importance. Columns are obviously for tables. Every time we create a variable, we also need to assign a data type to it. In addition to those, we have expressions and parameters to conclude the list of objects that are going to hold data and therefore we need to specify what kind of data they will contain.

这四种SQL数据类型的对象具有最高的重要性。 列显然用于表。 每次创建变量时，我们还需要为其分配数据类型。 除此之外，我们还有表达式和参数来总结要保存数据的对象列表，因此我们需要指定它们将包含哪种数据。

Moving on, let’s see the three categories of data types:

继续，让我们看一下数据类型的三类：

• Built-in data types
  内置数据类型
• User-defined alias data types
  用户定义的别名数据类型
• User-defined common language runtime (CLR) data types
  用户定义的公共语言运行时（CLR）数据类型

There is not much to say about the first category. These are data types that we’re all used to. Below is a chart that lists well known built-in data types and their ranges:

关于第一类没有太多要说的。 这些是我们都习惯的数据类型。 下面的图表列出了众所周知的内置数据类型及其范围：

Data category	SQL data type	Size	Value range
Exact numeric	Bit	1	1, 0, or NULL
	Tinyint	1	0 to 255
	Smallint	2	-2^15 (-32,768) to 2^15-1 (32,767)
	Int	4	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
	Bigint	8	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
	Smallmoney	4	– 214,748.3648 to 214,748.3647
	Money	8	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
	numeric[ (p[ ,s] )]	5-17
	decimal [ (p[ ,s] )]	5-17
Approximate numeric	Float	4-8	– 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
	Real/float(24)	4	– 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38
Character strings	char [ ( N ) ]	N	N = 1 to 8000 non-Unicode characters bytes
	nvarchar [ ( N | max ) ]	N or 2^31-1	N = 1 to 8000 non-Unicode characters bytes Max = 2^31-1 bytes (2 GB) non-Unicode characters bytes
	Text	2^31-1	1 to 2^31-1 (2,147,483,647) non-Unicode characters bytes
Unicode character strings	nchar [ ( N ) ]	N	N = 1 to 4000 UNICODE UCS-2 bytes
	nvarchar [ ( N | max ) ]	N or 2^31-1	N = 1 to 4000 UNICODE UCS-2 bytes 1 to 2^31-1 (2,147,483,647) UNICODE UCS-2 bytes
	Ntext	2^30-1	Maximum size 2^30 – 1 (1,073,741,823) bytes
Binary strings	binary [ ( N ) ]	N	N = 1 to 8000 bytes
	varbinary [ ( N | max) ]	N or 2^31-1	N = 1 to 8000 bytes Max = 0 to 2^31-1 bytes
	Image	2^31-1	0 to 2^31-1 (2,147,483,647) bytes
Other data types	Uniqueidentifier	16	xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (hex decimal)
	Timestamp	8	binary(8) or varbinary(8)
	rowversion	8	binary(8) or varbinary(8)
	xml	2^31-1	xml( [ CONTENT | DOCUMENT ] xml_schema_collection )
	sql_variant	8016	data type that stores values of various SQL Server-supported data types
	Hierarchyid	892	6*logAn bits where n is child node
	Cursor
	Table
	Sysname	256
Date and time	Date	3	0001-01-01 through 9999-12-31
	time [ (fractional second precision) ]	3 to 5	00:00:00.0000000 through 23:59:59.9999999
	Smalldatetime	4	Date: 1900-01-01 through 2079-06-06 Time: 00:00:00 through 23:59:59
	Datetime	8	Date: January 1, 1753, through December 31, 9999 Time: 00:00:00 through 23:59:59.997
	datetime2 [ (fractional seconds precision) ]	6 to 8	Date: 0001-01-01 through 9999-12-31 Time: 00:00:00 through 23:59:59.9999999
	datetimeoffset [ (fractional seconds precision) ]	8 to 10	Date: 0001-01-01 through 9999-12-31 Time: 00:00:00 through 23:59:59.9999999 Time zone offset: -14:00 through +14:00
Spatial