SQL Server Error Messages - Msg 479

Error Message

Server: Msg 479, Level 16, State 2, Line 1
Invalid ROWS value or REPEATABLE seed "<Sample Number>" in the TABLESAMPLE clause for table <Table Name>.  
The value or seed must be greater than 0.

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> ) ]

The <sample_number> is an exact or approximate constant numeric expression that represents the percent or number of rows to sample. When ROWS is specified, the <sample_number> expression must evaluate to an integer value greater than zero, otherwise, this error message will be generated.

To illustrate, the following SELECT statement will generate this error as the <sample_number> specified is not greater than zero and ROWS is specified:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (0 ROWS)

Msg 479, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed "0" in the TABLESAMPLE clause for table "dbo.Contact".
The value or seed must be greater than 0.

As another example, the following SELECT statement tries to return 10 rows from the table and having the result set in reverse order:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (-10 ROWS)

Msg 479, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed "-10" in the TABLESAMPLE clause for table "dbo.Contact".
The value or seed must be greater than 0.

The <repeat_seed> is a constant integer expression used by SQL Server to generate a random number. The <repeat_seed> is of BIGINT data type and must evaluate to an integer greater than zero, otherwise this error will be generated as well.

To illustrate, the following SELECT statement will generate this error as the specified is not greater than zero:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (100 ROWS) REPEATABLE ( 0 )

Msg 479, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed "0" in the TABLESAMPLE clause for table "dbo.Contact".
The value or seed must be greater than 0.

As another example, the following SELECT statement will generate this error as a negative value is passed as the <repeat_seed>:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (100 ROWS) REPEATABLE ( -10 )

Msg 479, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed "-10" in the TABLESAMPLE clause for table "dbo.Contact".
The value or seed must be greater than 0.

Solution / Work Around

To avoid this error, as the error message suggests, make sure that the value passed as the <sample_number> or as the <repeat_seed> is greater than 0. Using the samples above, the following SELECT statements will avoid this error from being generated as a value greater than zero is passed as the <sample_number> and as the <repeat_seed>:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (1000 ROWS)

SELECT * FROM [dbo].[Contact] TABLESAMPLE (100 ROWS) REPEATABLE ( 100 )

As for the example earlier which tries to return the result set in reverse order, the following SELECT statement can be used to avoid generating this error and still achieve the requirement of returning 10 random rows from the table and have the result in reverse order based on one of the table columns:

SELECT * FROM [dbo].[Contact] TABLESAMPLE (10 ROWS)
ORDER BY [ContactID] 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 10 * 
      FROM [dbo].[Contact]
      ORDER BY NEWID()) A
ORDER BY [ContactID] DESC