Ограничение результирующего набора с помощью предложения TABLESAMPLE

Изменения: 12 декабря 2006 г.

Предложение 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) ]

Примечание.
При применении TABLESAMPLE к базам данных, обновленных до версии SQL Server 2005, уровень совместимости базы данных должен быть установлен в значение 90. Сведения об установке уровня совместимости баз данных см. в разделе sp_dbcmptlevel (Transact-SQL).

TABLESAMPLE может использоваться для быстрой выборки из большой таблицы, если выполняется одно из следующих условий.

• Выборка не является по-настоящему случайной на уровне отдельных строк.
• Строки на отдельных страницах таблицы не соотносятся с другими строками на одной и той же странице.

Важно!

Если необходима действительно случайная выборка отдельных строк, следует изменить запрос так, чтобы он отфильтровывал строки по случайному признаку, не используя 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) является случайное значение типа float в диапазоне от 0 до 1.

Использование параметра SYSTEM

Параметр SYSTEM соответствует методу выборки, зависящему от реализации ANSI SQL. Параметр SYSTEM необязателен, однако он является единственным доступным в SQL Server 2005 методом выборки и используется по умолчанию.

TABLESAMPLE SYSTEM возвращает примерное процентное соотношение строк и формирует случайное значение для каждой физической страницы (размером 8 КБ) в таблице. В зависимости от случайного значения и указанного процентного соотношения страница либо включается в выборку, либо исключается из нее. Если страница включается, все ее строки попадают в образец результирующего набора. Например, если задано предложение TABLESAMPLE SYSTEM 10 PERCENT, SQL Server возвращает все строки для приблизительно 10% всех страниц данных указанной таблицы. Если строки равномерно распределены по страницам таблицы и она содержит достаточно страниц, количество строк в результирующем наборе должно быть приблизительно равно размеру запрошенной выборки. Тем не менее, поскольку полученное для каждой из страниц случайное значение не зависит от значений для других страниц, то количество страниц в результирующем наборе, возможно, будет больше или меньше запрошенного. Для ограничения максимального количества строк используется оператор TOP(n).

Если указано число строк, а не процентное соотношение на основе общего числа строк в таблице, это число сначала преобразуется к процентному соотношению возвращаемых строк (страниц). Затем операция TABLESAMPLE выполняется с использованием полученного значения.

Если таблица состоит из одной страницы, возвращаются либо все строки, либо ни одной. В этом случае TABLESAMPLE SYSTEM может возвратить только 100 или 0 процентов строк на странице, независимо от действительного их количества на странице.

Если в запросе к заданной таблице указано предложение TABLESAMPLE SYSTEM, план выполнения может содержать только просмотр таблицы (просмотр кучи или кластеризованного индекса, если он существует). Хотя план показывает, что осуществляется просмотр таблицы, из файла данных требуется считывать только те страницы, которые должны быть включены в результирующий набор.

Важно!

Предложение TABLESAMPLE SYSTEM следует использовать осторожно, учитывая возможные последствия работы с выборками. Например, при соединении двух таблиц, скорее всего, будет возвращено соответствие для каждой строки таблицы, но если для каждой из таблиц задано TABLESAMPLE SYSTEM, то строки, возвращаемые из таблицы, в которой не выполнялась выборка, редко будут иметь совпадающие строки в таблице, выборка в которой выполнялась. Это может вызвать подозрение в том, что в базовых таблицах имеется проблема несогласованности данных, хотя это не так. Аналогичным образом, если предложение TABLESAMPLE SYSTEM указано для каждой из соединяемых таблиц, положение, вероятно, только ухудшится.

Использование параметра REPEATABLE

Использование параметра REPEATABLE приводит к повторному возвращению заданной выборки. Если параметр REPEATABLE указывается с тем же значением repeat_seed, компонент SQL Server возвращает то же подмножество строк (если только в таблице не произошли изменения). Если параметр REPEATABLE указывается с другим значением repeat_seed, компонент SQL Server обычно возвращает другой набор строк таблицы. Изменениями считаются следующие действия над таблицей: вставка, обновление, удаление, перестройка или дефрагментация индекса, восстановление или присоединение базы данных.

Примеры

А. Выбор процента строк

Таблица Person.Contact содержит 19 972 строк. В следующем примере возвращаются около 10 процентов строк. Количество возвращаемых строк обычно изменяется при каждом выполнении инструкции.

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

Б. Выбор процента строк с начальным значением

В следующем примере возвращается тот же самый набор строк при каждом выполнении инструкции. Начальное значение 205 было выбрано произвольно.

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

В. Выбор количества строк

В следующем примере возвращаются приблизительно 100 строк. Фактическое количество возвращаемых строк может значительно изменяться. При указании небольшого количества, например 5, результат выборки может быть нулевым.

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

См. также

Другие ресурсы

FROM (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Новое содержимое Добавлены примеры.
14 апреля 2006 г.	Измененное содержимое Внесены изменения в рекомендации по использованию TABLESAMPLE и пояснения относительно того, что результаты TABLESAMPLE не являются случайными на уровне отдельных строк. Добавлен пример программного кода, показывающий, как произвести случайную выборку на уровне отдельных строк в качестве альтернативы TABLESAMPLE.