使用 TABLESAMPLE 限制结果集

更新日期: 2006 年 12 月 12 日

TABLESAMPLE 子句将从 FROM 子句中的表返回的行数限制到样本数或行数的某一百分比。例如:

TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.

不能将 TABLESAMPLE 应用于派生表、链接服务器中的表以及通过表值函数、行集函数或 OPENXML 派生的表。不能在视图或内联表值函数的定义中指定 TABLESAMPLE。

TABLESPACE 子句的语法如下:

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )

[ REPEATABLE (repeat_seed) ]

ms189108.note(zh-cn,SQL.90).gif注意:
对升级到 SQL Server 2005 的数据库使用 TABLESAMPLE 时,数据库的兼容级别必须设置为 90。若要设置数据库兼容级别,请参阅 sp_dbcmptlevel (Transact-SQL)

当下列任一条件为真时,可以使用 TABLESAMPLE 从大型表中快速返回样本:

  • 样本不必是单个行级别的真正随机抽样。
  • 该表各页上的行不必与同一页上的其他行相关联。
ms189108.note(zh-cn,SQL.90).gif重要提示:
如果确实需要单个行的随机抽样,则应修改查询以随机筛选出行,而不是使用 TABLESAMPLE。例如,以下查询使用 NEWID 函数返回 Sales.SalesOrderDetail 表的大约百分之一的行: SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) SalesOrderID 列包括在 CHECKSUM 表达式中,从而 NEWID() 每次计算一行以获取每行抽样。表达式 CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) 的计算结果为介于 0 和 1 之间的随机 float 值。

使用 SYSTEM 选项

SYSTEM 指定与 ANSI SQL 实现相关的抽样方法。指定 SYSTEM 是可选的,但是此选项是 SQL Server 2005 中唯一可用的抽样方法,并且是默认应用的方法。

TABLESAMPLE SYSTEM 返回行的近似的百分比,并针对表中每个物理 8 KB 页生成随机值。样本中可以包括页,也可以不包括页,具体情况根据页的随机值以及查询中指定的百分比来确定。样本中包含的每一页都返回样本结果集中的所有行。例如,如果指定 TABLESAMPLE SYSTEM 10 PERCENT,则 SQL Server 返回该表的大约 10% 的指定数据页中的所有行。如果这些行均匀分布在表的各页上,并且表中存在足够多的页,则返回的行数应接近所需样本的大小。但是,由于针对每页生成的随机值与针对任何其他页生成的值无关,因此,返回的页百分比可能会大于或小于所需的百分比。可以使用 TOP(n) 运算符将行数限制到指定的最大数。

指定行数而不是指定基于表中总行数的百分比时,此数将转换为行数的百分比,进而转换为应返回的页数的百分比。然后使用此计算得到的百分比执行 TABLESAMPLE 操作。

如果表由一页组成,则返回此页上的所有行或不返回任何行。在这种情况下,TABLESAMPLE SYSTEM 只能返回页上的 100% 或 0% 行,而无论页上的行数是多少。

针对特定表使用 TABLESAMPLE SYSTEM,限制了在此表中使用表扫描计划的执行(如果存在堆或聚集索引,则为堆的扫描或聚集索引的扫描)。尽管计划显示已执行表扫描,但实际上只需要从数据文件中读取结果集中包含的那些页。

ms189108.note(zh-cn,SQL.90).gif重要提示:
使用 TABLESAMPLE SYSTEM 子句时应谨慎,还应了解使用样本的某些影响。例如,两个表的联接可能返回两个表中每行的匹配行;但是,如果对两表中任意一个指定 TABLESAMPLE SYSTEM,则从未抽样表中返回的某些行不大可能具有抽样表中的匹配行。此行为可能使您怀疑基础表中是否存在数据一致性的问题,但实际上数据是有效的。同样,如果针对联接的两个表指定 TABLESAMPLE SYSTEM,则发现的问题可能会更严重。

使用 REPEATABLE 选项

REPEATABLE 选项导致再次返回选定的样本。使用同一个 repeat_seed 值指定 REPEATABLE 时,只要未对表进行任何更改,SQL Server 将返回相同的行子集。使用其他 repeat_seed 值指定 REPEATABLE 时,SQL Server 通常将返回表中行的不同样本。对表的以下操作将视为更改:插入、更新、删除、索引重建、索引碎片整理、还原数据库和附加数据库。

示例

A. 选择行的百分比

Person.Contact 表包含 19,972 行。以下示例返回大约 10% 的行。每次执行此语句时,返回的行数通常都不同。

USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact 
TABLESAMPLE (10 PERCENT) ;

B. 选择带有种子值的行的百分比

每次执行时,以下示例均返回同一行集。种子值 205 是任意选择的。

USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact 
TABLESAMPLE (10 PERCENT) 
   REPEATABLE (205) ;

C. 选择若干行

以下示例返回大约 100 行。实际返回的行数可能会有很大差异。如果指定较小的数值,例如 5,则在示例中可能收不到任何结果。

USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact 
TABLESAMPLE (100 ROWS) ;

请参阅

其他资源

FROM (Transact-SQL)

帮助和信息

获取 SQL Server 2005 帮助

更改历史记录

发布日期 历史记录

2006 年 12 月 12 日

新增内容:
  • 添加了示例。

2006 年 4 月 14 日

更改的内容:
  • 修改了有关何时使用 TABLESAMPLE 的建议,并阐明了单个行级别的 TABLESAMPLE 的结果不是随机的。添加了显示如何返回作为使用 TABLESAMPLE 的替代的单个行级别的随机抽样的示例代码。