SQL Server Error Messages - Msg 476

Error Message

Server: Msg 476, Level 15, State 1, Line 1
Invalid PERCENT tablesample size "<sample_number>" for table "<Table Name>".
The PERCENT tablesample size must be between 0 and 100.

Causes

The TABLESAMPLE clause, introduced in SQL Server 2005, limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. The syntax for the TABLESAMPLE clause is as follows:

TABLESAMPLE ( sample_number [PERCENT | ROWS] ) [ REPEATABLE ( repeat_seed ) ]

If PERCENT is specified in the TABLESAMPLE clause, the sample_number value must be between 0 and 100, otherwise this error will be generated.

To illustrate, the following SELECT statement tries to return all rows in a table twice in random order:

SELECT * FROM [dbo].[OrderDetails] TABLESAMPLE ( 200 PERCENT )

Server: Msg 476, Level 15, State 0, Line 1
Invalid PERCENT tablesample size "200.000000" for table "dbo.OrderDetails".
The PERCENT tablesample size must be between 0 and 100.

As another example, the following SELECT statement tries to return 5 percent of the rows from the table and return the result in reverse order:

SELECT * FROM [dbo].[OrderDetails] TABLESAMPLE ( -5 PERCENT )

Server: Msg 476, Level 15, State 0, Line 1
Invalid PERCENT tablesample size "-5.000000" for table "dbo.OrderDetails".
The PERCENT tablesample size must be between 0 and 100.

Solution / Work Around

To avoid this error, as the error message suggests, make sure that the value passed to the PERCENT clause of the TABLESAMPLE clause is a number between 0 and 100. Using the sample above, the following SELECT statement will avoid the error generated earlier and still achieve the requirement of returning all rows from the table twice in random order:

SELECT * FROM [dbo].[OrderDetails] TABLESAMPLE ( 100 PERCENT )
UNION ALL
SELECT * FROM [dbo].[OrderDetails] TABLESAMPLE ( 100 PERCENT )

Another way of achieving this requirement without the use of TABLESAMPLE is with the use of the ORDER BY NEWID():

SELECT * 
FROM (SELECT TOP 100 PERCENT *
      FROM [dbo].[OrderDetails]
      ORDER BY NEWID()) A
UNION ALL
SELECT *
FROM (SELECT TOP 100 PERCENT *
      FROM [dbo].[OrderDetails]
      ORDER BY NEWID()) B

As for the second example, the following SELECT statement will avoid the error generated and still achieve the requirement of returning 5 percent of the rows from the table and have the result in reverse order:

SELECT * FROM [dbo].[OrderDetails] TABLESAMPLE ( 5 PERCENT )
ORDER BY [OrderDate] DESC

Another way of achieving this requirement without the use of the TABLESAMPLE clause is with the use of the ORDER BY NEWID():

SELECT *
FROM (SELECT TOP (5) PERCENT * 
      FROM [dbo].[OrderDetails]
      ORDER BY NEWID()) A
ORDER BY [OrderDate] DESC