Sử dụng tablesample trong SQL Server

Xem xét ví dụ dưới đây, bao nhiêu dòng được trả về với mệnh đề tablesample

USE TempDB

go 

CREATE TABLE #Emp
    (
      empID INT,
      eName VARCHAR(100)
    )

go 

INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 1, 'eName1' ) 

INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 2, 'eName2' ) 

INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 3, 'eName3' ) 

INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 4, 'eName4' ) 

INSERT INTO #Emp
        ( empID, eName )
    VALUES ( 5, 'eName5' ) 

go 

SELECT empid, ename
FROM #Emp TABLESAMPLE (4 ROWS)

go 

Câu trả lời là có 5 dòng được trả về.

Nguyên văn giải thích bằng tiếng Anh

Explanation:

Even when ROWS option is applied, TABLESAMPLE clause does not select data row by row, instead each 8-KB physical page is included or excluded entirelly. If data fits into a single page and if TABLESAMPLE clause includes that page for output , all the rows in that page would be outputted - in this case 5. If that page is excluded by TABLESAMPLE, zero rows would be outputted. Note:- When number of rows is specified with TABLESAMPLE, that number is converted into a percentage of the rows, and TABLESAMPLE operation is performed with that computed percentage. For further details, please refer http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx