TABLESAMPLE을 사용하여 결과 집합 제한
TABLESAMPLE 절은 FROM 절의 테이블에서 반환되는 행의 수를 샘플 수 또는 행의 PERCENT로 제한합니다. 예를 들면 다음과 같습니다.
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. */.
파생된 테이블, 연결된 서버의 테이블 및 테이블 반환 함수, 행 집합 함수 또는 OPENXML에서 파생된 테이블에는 TABLESAMPLE을 적용할 수 없습니다. 뷰 정의 또는 인라인 테이블 반환 함수에서는 TABLESAMPLE을 지정할 수 없습니다.
TABLESPACE 절의 구문은 다음과 같습니다.
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]
[!참고]
TABLESAMPLE은 SQL Server 2005에서 도입되었습니다. 이전 버전에서 업그레이드되는 데이터베이스에 대해 TABLESAMPLE을 사용할 경우 데이터베이스의 호환성 수준을 90 이상으로 설정해야 합니다. 데이터베이스 호환성 수준을 설정하려면 ALTER DATABASE(Transact-SQL)을 참조하십시오.
다음 조건 중 하나에 해당하는 경우 TABLESAMPLE을 사용하여 큰 테이블에서 샘플을 신속하게 반환할 수 있습니다.
샘플은 개별 행 수준에서 무작위 샘플링이 아니어도 됩니다.
테이블의 개별 페이지에 있는 행은 같은 페이지의 다른 행과 상관 관계가 없습니다.
.gif "중요 정보") 중요 |
|---|
개별 행의 무작위 샘플링을 원하는 경우 TABLESAMPLE을 사용하는 대신 임의로 행을 필터링하도록 쿼리를 수정합니다. 예를 들어 다음 쿼리는 NEWID 함수를 사용하여 Sales.SalesOrderDetail 테이블에서 약 1%에 해당하는 행을 반환합니다. SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) 행별 샘플링을 얻기 위해 NEWID()가 각 행에 대해 한 번 평가되도록 SalesOrderID 열은 CHECKSUM 식에 포함되어 있습니다. CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) 식은 0에서 1 사이의 임의 float 값으로 평가됩니다. |
SYSTEM 옵션 사용
SYSTEM은 ANSI SQL 구현에 종속적인 샘플링 방법을 지정합니다. SYSTEM 옵션은 선택 사항이지만 SQL Server에서 사용할 수 있는 유일한 샘플링 방법이며 기본적으로 적용됩니다.
TABLESAMPLE SYSTEM은 행의 대략적인 백분율을 반환하며 테이블에서 각 8KB의 실제 페이지에 대해 임의 값을 생성합니다. 페이지에 대한 임의 값과 쿼리에서 지정한 비율을 기준으로 샘플에 페이지가 포함되거나 제외됩니다. 포함된 각 페이지는 샘플 결과 집합에 모든 행을 반환합니다. 예를 들어 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에서 테이블의 다른 행 샘플이 반환될 가능성이 있습니다. 이 경우 테이블을 변경하는 것으로 간주되는 동작으로는 삽입, 업데이트, 삭제, 인덱스 다시 작성, 인덱스 조각 모음, 데이터베이스 복원 및 데이터베이스 연결 등이 있습니다.
예
1. 특정 비율의 행 선택
Person.Person 테이블에 19,972개의 행이 있습니다. 다음 문에서는 약 10%의 행을 반환합니다. 반환되는 행 수는 일반적으로 문이 실행될 때마다 변경됩니다.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;
2. 초기값이 있는 특정 비율의 행 선택
다음 문에서는 문이 실행될 때마다 동일한 행 집합을 반환합니다. 초기값 205가 임의로 선택되었습니다.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;
3. 특정 개수의 행 선택
다음 문에서는 약 100개의 행이 반환됩니다. 반환되는 실제 행 수는 크게 다를 수 있습니다. 5와 같은 작은 수를 지정하는 경우 샘플의 결과를 얻지 못할 수 있습니다.
USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;