表

1、表计划指南

在设计数据库时，必须先确定数据库所需的表、每个表中数据的类型以及可以访问每个表的用户。在创建表及其对象之前，最好先规划出您的计划并确定表的下列特征：

        表要包含的数据的类型。

        表中的列数，每一列中数据的类型和长度（如果必要）。

        哪些列允许空值。

         是否要使用以及何处使用约束、默认设置和规则。

所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。

创建表的最有效的方法是同时定义表中所需的所有内容。这些内容包括表的数据限制和其他组件。在创建和操作表后，将对表进行更为细致的设计。

创建表的有用方法是：创建一个基表，向其中添加一些数据，并使用这个基表一段时间。这种方法使您可以在添加各种约束、索引、默认设置、规则和其他对象形成最终设计之前，发现哪些事务最常用，哪些数据经常输入。

2、设计表时首先要执行的操作之一是为每个列指定数据类型。

数据类型定义了各列允许使用的数据值。通过下列方法之一可以为列指定数据类型：

        使用 SQL Server 系统数据类型。

        创建基于系统数据类型的别名数据类型。

        从在 Microsoft .NET Framework 公共语言运行时中创建的类型中创建用户定义类型。

例如，如果希望列中只含有名称，则可以将一种字符数据类型指定给列。同样，如果希望列中只包含数字，则可以指定一种 numeric 数据类型。有关数据类型的详细信息，请参阅数据类型（数据库引擎）。

SQL Server 还支持几种基本数据类型的 SQL-92 同义词。有关详细信息，请参阅数据类型同义词 (Transact-SQL)。

强制数据完整性

系统、别名和用户定义类型可用于强制数据完整性。这是因为输入或更改的数据必须符合原始 CREATE TABLE 语句中指定的类型。例如，无法在定义为 datetime 的列中存储姓氏，因为 datetime 列只接受有效日期。通常，将数值数据存储在数字列中，尤其以后必须计算数值数据时。

3、行内数据

小到中等大小的大值类型（varchar(max)、nvarchar(max)、varbinary(max) 和 xml）和大型对象 (LOB) 数据类型（text、ntext 和 image）都可以存储在数据行中。该行为可以通过在 sp_tableoption 系统存储过程中使用以下两个选项来控制：用于大值类型的 large value types out of row 选项，以及用于大型对象类型的 text in row 选项。这两个选项最适用于这样的表：其中上述任意一种数据类型的数据值通常在一个单元中读/写，并且引用表的大多数语句都将引用此类数据。在行内存储的数据不一定有用，这取决于使用情况或工作负荷特征。

在 SQL Server 的未来版本中将删除 text in row 选项。避免在新的开发工作中使用该选项，并计划修改当前使用 text in row 的应用程序。建议使用 varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型存储大型数据。若要控制这些数据类型的行内和行外行为，请使用 large value types out of row 选项。

除非 text in row 选项设置为 ON 或特定的行内限制，否则 text、ntext 或 image 字符串都将是在数据行外存储的大型字符或二进制字符串（最多 2 GB）。数据行只包括一个 16 字节的文本指针，该指针指向一个内部指针构成的树的根节点。这些指针映射存储字符串片段的页。有关 text、ntext 或 image 字符串存储的详细信息，请参阅使用 Text 和 Image 数据。

可以为包含 LOB 数据类型列的表设置 text in row 选项。还可以指定 text in row 选项限制，范围从 24 到 7,000 字节。

同样，除非 large value types out of row 选项设置为 ON，否则会尽可能将 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 列存储在数据行内。如果如此设置，则可以的话 SQL Server 数据库引擎将尝试容纳此特定值，否则会将其推到行外。如果 large value types out of row 设置为 ON，则上述值将存储在行外而只有 16 字节的文本指针存储在记录中。

注意注意

当 large value types out of row 设置为 OFF 时，用于大型值数据类型的最大行内存储量设置为 8,000 字节。与 text in row 选项不同，您不能指定表中列的行内限制。

将表配置为直接在数据行中存储大型值类型或大型对象数据类型时，如果存在以下情况之一，实际的列值都将存储在行内：

字符串的长度小于为 text、ntext 和 image 列指定的限制值。

数据行中有足够的可用空间容纳字符串。

当大型值类型或大型对象数据类型列值存储在数据行中时，数据库引擎不必访问单独的页或页集来读/写字符或二进制字符串。这便使读/写行内字符串的速度与读/写大小受限制的 varchar、nvarchar 或 varbinary 字符串的速度大致一样。同样，当值存储在行外时，数据库引擎将引发读/写附加页。

对于大型对象数据类型，如果存储字符串所需的空间比 text in row 选项限制或行中的可用空间大，则本应存储在指针树根节点中的指针集将存储在行中。如果存在以下情况之一，指针将存储在行中：

存储指针所需的空间量比 text in row 选项限制指定的空间量小。

数据行中有足够的可用空间容纳指针。

当指针从根节点移至行本身时，数据库引擎不需要使用根节点。这样便可以在读/写字符串时不必访问页。从而可以提高性能。

如果使用根节点，它们将存储为 LOB 页中的一个字符串片段，并且最多可以包含 5 个内部指针。数据库引擎需要行具有 72 字节的空间来存储行内字符串的五个指针。如果 text in row 选项为 ON 或 large value types out of row 选项为 OFF 时行中没有足够的空间来容纳指针，数据库引擎可能必须分配一个 8K 的页来容纳它们。如果值的数据长度超过 40,200 字节，则需要 5 个以上的行内指针，此时只有 24 字节存储在主行中，而其他数据页被分配在 LOB 存储空间中。

当大型字符串存储在行中时，它们将与可变长度字符串的存储方式相似。数据库引擎将对列按大小以降序排序，并将值推到行外，直到剩余的列容纳在数据页 (8K) 中。

启用和禁用 large value types out of row 选项

可以按照下列方式通过使用 sp_tableoption 来为表启用 large value types out of row 选项：

 

复制

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

如果指定为 OFF，则 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 列的行内限制将设置为 8,000 个字节。只有 16 字节的根指针存储在行内，而值存储在 LOB 存储空间中。对于其中大多数语句都不引用大型值类型列的表，建议将该选项设置为 ON。将这些列存储在行外意味着每页可以容纳更多的行，因此减少了扫描表所需的 I/O 操作的数量。

当该选项的值设置为 OFF 时，许多字符串可能最终会存储在行内，从而可能减少每页上容纳的数据行数。如果大多数引用表的语句都不访问 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 列，则减少页中的行数会增加处理查询时必须读取的页数。减少每页上的行数会增加页的数量，优化器找不到可用的索引时可能必须扫描这些页。

也可以使用 sp_tableoption 禁用行外选项：

 

复制

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

如果 large value types out of row 选项的值发生更改，则现有的 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 类型的值不会立即转换。字符串的存储会随着字符串的更新而变化。任何插入表中的新值都将根据有效的表选项存储。

若要检查特定表的 large value types out of row 选项的值，请查询 sys.tables 目录视图的 large_value_types_out_of_row 列。如果表未启用 large value types out of row，则此列为 0；如果大值类型存储在行外，则此列为 1。

启用和禁用 text in row 选项

可以按照以下方式通过使用 sp_tableoption 为表启用 text in row 选项：

 

复制

sp_tableoption N'MyTable', 'text in row', 'ON'

或者，可以为能够在数据行中存储的 text、ntext 和 image 字符串长度指定从 24 到 7,000 字节的最大限制：

 

复制

sp_tableoption N'MyTable', 'text in row', '1000'

如果指定的是 ON 而不是一个特定的限制，则此限制的默认值为 256 字节。该默认值使您能够从使用 text in row 选项中获得最多的性能收益。虽然通常情况下，不应使该值的设置低于 72，但也不应将其设置的过高。该设置尤其适用于其中大多数语句都不引用 text、ntext 和 image 列的表，或其中有多个 text、ntext 和 image 列的表。

如果设置了较大的 text in row 限制，且行本身存储了许多字符串，则可以显著减少存储在每页上的数据行数。如果大多数引用表的语句都不访问 text、ntext 或 image 列，则减少页中的行数会增加处理查询时必须读取的页数。减少每页上的行会增加索引和页的大小，优化器找不到可用的索引时可能必须扫描这些索引和页。text in row 限制的默认值为 256，这足以确保小型字符串和根文本指针可以存储在行中，但不会使每页上的行减少太多以至影响性能。

对于具有表数据类型的变量和用户定义函数（返回表）返回的表，text in row 选项自动设置为 256。不能更改该设置。

也可以使用 sp_tableoption 指定 OFF 或 0 选项值来禁用此选项。

 

复制

sp_tableoption N'MyTable', 'text in row', 'OFF'

若要检查特定表的 text in row 选项的值，请查询 sys.tables 目录视图的 text_in_row_limit 列。如果没有为表启用 text in row，则此列为 0；如果设置了行内限制，则此列为大于 0 的值。

使用 text in row 选项的效果

 

text in row 选项具有以下效果：

启用 text in row 选项后，可以使用 TEXTPTR、READTEXT、UPDATETEXT 或 WRITETEXT 语句读取或修改表中存储的任何 text、ntext 或 image 值的部分。在 SELECT 语句中，可以读取整个 text、ntext 或 image 字符串，或使用 SUBSTRING 函数读取部分字符串。所有引用表的 INSERT 或 UPDATE 语句都必须指定完整的字符串，并且不能只修改 text、ntext 或 image 字符串的一部分。

当第一次启用 text in row 选项时，现有的 text、ntext 或 image 字符串不会立即转换为行内字符串。仅当随后更新字符串时，这些字符串才转换成行内字符串。启用 text in row 选项后插入的任何 text、ntext 或 image 字符串都将作为行内字符串插入。

禁用 text in row 选项可能是长时间运行的日志记录操作。表被锁定，并且所有行内 text、ntext 和 image 字符串都将转换为常规 text、ntext 和 image 字符串。运行命令所需的时间和已经修改的数据量，取决于必须从行内字符串转换为常规字符串的 text、ntext 和 image 字符串的数量。

text in row 选项不会影响 SQL Server Native Client OLE DB 访问接口或 SQL Server Native Client ODBC 驱动程序的操作，而只会加快访问 text、ntext 和 image 数据的速度。

启用 text in row 选项后，DB-Library 文本和图像函数（例如 dbreadtext 和 dbwritetext）将无法在表上使用。

4、行溢出数据超过 8 KB

一个表中的每一行最多可以包含 8,060 字节。在 SQL Server 2008 中，对于包含 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的表，可以放宽此限制。其中每列的长度仍必须在 8,000 字节的限制内，但是它们的总宽可以超过 8,060 字节的限制。创建和修改 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列以及更新或插入数据时，此限制适用于上述列。

注意注意

此限制不适用于 varchar(max)、nvarchar(max)、varbinary(max)、text、image 或 xml 列。有关这些列的存储的详细信息，请参阅使用大值数据类型、使用 Text 和 Image 数据和使用 XML 数据。

行溢出注意事项

当合并每行超过 8060 字节的 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时，请注意下列事项：

超过 8,060 字节的行大小限制可能会影响性能，因为 SQL Server 仍保持每页 8 KB 的限制。当合并 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列超过此限制时，SQL Server 数据库引擎 将把最大宽度的记录列移动到 ROW_OVERFLOW_DATA 分配单元的另一页上，而在原始页上保留一个 24 字节指针。如果更新操作使记录变长，大型记录将被动态移动到另一页。如果更新操作使记录变短，记录可能会移回 IN_ROW_DATA 分配单元中的原始页。此外，执行查询和其他选择操作（例如，对包含行溢出数据的大型记录进行排序或合并）将延长处理时间，因为这些记录将同步处理，而不是异步处理。

因此，当要设计的表中包含多个 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时，请考虑可能溢出的行的百分比，以及可能查询这些溢出数据的频率。如果可能需要经常查询行溢出数据中的许多行，请考虑对表格进行规范化处理，以使某些列移动到另一个表中。然后可以在异步 JOIN 操作中执行查询。

对于 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列，单个列的长度仍然必须在 8000 字节的限制之内。只有它们的合并长度可以超过表的 8060 字节的行限制。

其他数据类型列的和（包括 char 和 nchar 数据）必须在 8,060 字节的行限制之内。大型对象数据也不受 8,060 字节行限制的制约。

聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果对 varchar 列创建了聚集索引，并且在 IN_ROW_DATA 分配单元中存在现有数据，则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。有关分配单元的详细信息，请参阅表组织和索引组织。

可以包括包含行溢出数据的列，作为非聚集索引的键列或非键列。

对于使用稀疏列的表，记录大小限制为 8,018 字节。转换后的数据加上现有记录数据超过 8,018 字节时，会返回 MSSQLSERVER ERROR 576。列在稀疏和非稀疏类型之间转换时，数据库引擎会保留当前记录数据的副本。这样，记录所需的存储会临时加倍。

若要获得有关可能包含行溢出数据的表或索引的信息，请使用 sys.dm_db_index_physical_stats 动态管理函数。

5、自动编号列和标识符列

对于每个表，均可创建一个包含系统生成的序号值的标识符列，该序号值以唯一方式标识表中的每一行。例如，当在表中插入行时，标识符列可自动为应用程序生成唯一的客户回执编号。标识符列在其所定义的表中包含的值通常是唯一的。这意味着包含标识符列的其他表可包含与另一个表所用的标识值相同的标识值。但是，由于标识符值通常是在单个表的上下文中使用，并且标识符列与其他表中的标识符列不相关，因此通常不会出现问题。

每个表中均可创建一个全局唯一标识符列，该列中包含在全球联网的所有计算机中不重复的值。当必须合并来自多个数据库系统的相似数据时（例如，在一个客户帐单系统中，其数据位于世界各地的分公司），通常需要保证列包含全局唯一值。当数据被汇集到中心以进行合并和制作报表时，使用全局唯一值可防止不同国家/地区的客户具有相同的帐单号或客户 ID。

SQL Server 使用 GUID 列进行合并复制和事务复制，同时更新订阅，以确保表的多个副本中的各行是唯一标识的。

请参阅

6、计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。

例如，在 AdventureWorks2008R2 示例数据库中，Sales.SalesOrderHeader 表的 TotalDue 列具有以下定义：TotalDue AS Subtotal + TaxAmt + Freight。

除非另行指定，否则计算列是未实际存储在表中的虚拟列。每当在查询中引用计算列时，都将重新计算它们的值。数据库引擎在 CREATE TABLE 和 ALTER TABLE 语句中使用 PERSISTED 关键字来将计算列实际存储在表中。如果在计算列的计算更改时涉及任何列，将更新计算列的值。通过将计算列标记为 PERSISTED，可以对具有确定性但不精确的计算列创建索引。另外，如果计算列引用 CLR 函数，则数据库引擎不能验证该函数是否真正具有确定性。在这种情况下，计算列必须为 PERSISTED，以便可对其创建索引。有关详细信息，请参阅 为计算列创建索引。

注意注意

必须对用作已分区表的分区依据列的任何计算列进行显式持久化。

计算列可用于选择列表、WHERE 子句、ORDER BY 子句或任何可使用正则表达式的其他位置，但下列情况除外：

用作 CHECK、FOREIGN KEY 或 NOT NULL 约束的计算列必须标记为 PERSISTED。如果计算列的值由具有确定性的表达式定义，并且索引列中允许使用计算结果的数据类型，则可将该列用作索引中的键列，或者用作 PRIMARY KEY 或 UNIQUE 约束的一部分。

例如，如果表中含有整数列 a 和 b，则可以对计算列 a + b 创建索引，但不能对计算列 a + DATEPART(dd, GETDATE()) 创建索引，因为在后续调用中，其值可能发生改变。

计算列不能作为 INSERT 或 UPDATE 语句的目标。

数据库引擎基于使用的表达式自动确定计算列的为 Null 性。即使只有非空列，大多数表达式的结果也“认为”可为空值，因为下溢或溢出生成的结果也可能为空。使用带 AllowsNull 属性的 COLUMNPROPERTY 函数可查明表中任何计算列的为 Null 性。通过指定 ISNULL (check_expression**,**constant) 可以将可为空值的表达式转换为不可为空值的表达式，其中, constant 是可替换所有空结果的非空值.

7、强制数据完整性

计划和创建表要求标识列的有效值，并确定强制列中数据完整性的方式。SQL Server 提供了下列机制来强制列中数据的完整性：

PRIMARY KEY 约束

FOREIGN KEY 约束

UNIQUE 约束

CHECK 约束

DEFAULT 定义

允许空值

• PRIMARY KEY 约束
• 表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY 约束来创建主键。
• 一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性，因此经常对标识列定义这种约束。
• 如果为表指定了 PRIMARY KEY 约束，则 数据库引擎将通过为主键列创建唯一索引来强制数据的唯一性。当在查询中使用主键时，此索引还可用来对数据进行快速访问。因此，所选的主键必须遵守创建唯一索引的规则。
• 如果对多列定义了 PRIMARY KEY 约束，则一列中的值可能会重复，但来自 PRIMARY KEY 约束定义中所有列的任何值组合必须唯一。
• 如下图所示，Purchasing.ProductVendor 表中的 ProductID 和 VendorID 列构成了针对此表的复合 PRIMARY KEY 约束。这确保了 ProductID 和 VendorID 的组合是唯一的。
• 
• 当进行联接时，PRIMARY KEY 约束将一个表与另一个表关联。例如，若要确定哪些供应商供应哪些产品，可以在 Purchasing.Vendor 表、Production.Product 表和 Purchasing.ProductVendor 表之间使用一个三向联接。因为 ProductVendor 包含 ProductID 和 VendorID 列，所以可通过与 ProductVendor 的联系来访问 Product 表和 Vendor 表。

②FOREIGN KEY 约束

外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREIGN KEY 约束来创建外键。

在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。

例如，因为销售订单和销售人员之间存在一种逻辑关系，所以 AdventureWorks2008R2 数据库中的 Sales.SalesOrderHeader 表含有一个指向 Sales.SalesPerson 表的链接。SalesOrderHeader 表中的 SalesPersonID 列与 SalesPerson 表中的主键列相对应。SalesOrderHeader 表中的 SalesPersonID 列是指向 SalesPerson 表的外键。

SalesOrderHeader.SalesPersonID 为外键。

FOREIGN KEY 约束并不仅仅可以与另一表的 PRIMARY KEY 约束相链接，它还可以定义为引用另一表的 UNIQUE 约束。FOREIGN KEY 约束可以包含空值，但是，如果任何组合 FOREIGN KEY 约束的列包含空值，则将跳过组成 FOREIGN KEY 约束的所有值的验证。若要确保验证了组合 FOREIGN KEY 约束的所有值，请将所有参与列指定为 NOT NULL。

注意注意

FOREIGN KEY 约束可以引用同一数据库的表中的列或同一表中的列。这些称为“自引用”表。例如，请考虑包含三列的一个雇员表：employee_number、employee_name 和 manager_employee_number。由于经理本身也是雇员，所以从 manager_employee_number 列到 employee_number 列存在外键关系。

引用完整性

尽管 FOREIGN KEY 约束的主要目的是控制可以存储在外键表中的数据，但它还可以控制对主键表中数据的更改。例如，如果在 Sales.SalesPerson 表中删除一个销售人员行，而这个销售人员的 ID 由 Sales.SalesOrderHeader 表中的销售订单使用，则这两个表之间关联的完整性将被破坏；SalesOrderHeader 表中删除的销售人员的销售订单因为与 SalesPerson 表中的数据没有链接而变得孤立了。

FOREIGN KEY 约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效，则这种更改将无法实现，从而确保了引用完整性。如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的 FOREIGN KEY 约束中的值相对应，则该操作将失败。若要成功更改或删除 FOREIGN KEY 约束的行，必须先在外键表中删除或更改外键数据，这将把外键链接到不同的主键数据上去。

对 FOREIGN KEY 约束建立索引

由于以下原因，对外键创建索引通常是有用的：

对 PRIMARY KEY 约束的更改可由相关表中的 FOREIGN KEY 约束检查。

当在查询中组合相关表中的数据时，经常在联接条件中使用外键列，方法是将一个表的 FOREIGN KEY 约束中的列与另一个表中的主键列或唯一键列匹配。索引使 数据库引擎可以在外键表中快速查找相关数据。但是，创建此索引并不是必需的。即使没有对两个相关表定义 PRIMARY KEY 或 FOREIGN KEY 约束，也可以对来自这两个表中的数据进行组合，但两个表间的外键关系说明已用其键作为条件对其进行了优化，以便组合到查询中。有关在联接中使用 FOREIGN KEY 约束的详细信息，请参阅联接基础知识和查询类型和索引。

表中的 FOREIGN KEY 约束数

SQL Server 对一个表可以包含的 FOREIGN KEY 约束（引用其他表）数没有预定义限制，对引用特定表的其他表所拥有的 FOREIGN KEY 约束数也没有预定义的限制。但是，实际的 FOREIGN KEY 约束数会受到硬件配置以及数据库和应用程序的设计的限制。建议表中包含的 FOREIGN KEY 约束不要超过 253 个，并且引用该表的 FOREIGN KEY 约束也不要超过 253 个。在设计数据库和应用程序时应考虑强制 FOREIGN KEY 约束的开销。等