|
|
Error Message
Server: Msg 477, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE
clause for table "<Table Name>". The value or seed
must be an integer.
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 ROWS is specified in the TABLESAMPLE clause, the sample_number must be an integer value. The same goes for the repeat_seed, which must be an integer value as well.
Given this, the following SELECT statements will generate this error message:
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 1000.50 ROWS )
Msg 477, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.Customers".
The value or seed must be an integer.
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100.0 )
Msg 477, Level 15, State 0, Line 1
Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.Customers".
The value or seed must be an integer.
Another way of generating this error is when using system functions. Even if the return value of the system function is an integer, this error message may still be encountered as seen from the following script:
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( DAY(GETDATE()) ROWS )
Msg 477, Level 15, State 1, Line 1
Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.Customers".
The value or seed must be an integer.
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 100 ROWS ) REPEATABLE ( YEAR(GETDATE()) )
Msg 477, Level 15, State 1, Line 1
Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.Customers".
The value or seed must be an integer.
Solution / Work Around:
To avoid this error, make sure that the values passed to the ROWS clause or to the REPEATABLE clause of the TABLESAMPLE clause is an integer value. Using the samples above, the following SELECT statements will avoid the errors generated earlier:
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 1000 ROWS )
SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100 )
If the values to be passed to the ROWS or REPEATABLE clause are going to come from a function or another source, one way to do this will be with the use of dynamic SQL statements. Using the samples above that uses the DAY and YEAR system functions, the following script can be used in its place which uses dynamic SQL executed using the EXECUTE statement:
DECLARE @SQLStmt VARCHAR(100)
SET @SQLStmt = 'SELECT * FROM [dbo].[Customers]
TABLESAMPLE ( ' + CAST(DAY(GETDATE()) AS VARCHAR(10)) + ' ROWS ) '
EXECUTE ( @SQLStmt )
DECLARE @SQLStmt VARCHAR(100)
SET @SQLStmt = 'SELECT * FROM [dbo].[Customers] TABLESAMPLE ( 100 ROWS )
REPEATABLE ( ' + CAST(YEAR(GETDATE()) AS VARCHAR(10)) + ')'
EXECUTE ( @SQLStmt )
Since dynamic SQL can now be used to set any value for the ROWS or REPEATABLE clause of the TABLESAMPLE, the value doesn’t have to come from a system function but can also come from any source such as a table or a local variable. Here’s an example:
DECLARE @SQLStmt VARCHAR(100)
DECLARE @RowCount INT
SELECT @RowCount = COUNT(*) FROM [dbo].[Customers]
SET @SQLStmt = 'SELECT * FROM [dbo].[Customers]
TABLESAMPLE ( ' + CAST(@RowCount / 10 + 1 AS VARCHAR(10)) + ' ROWS ) '
EXECUTE ( @SQLStmt )
Lastly, if the purpose of using the TABLESAMPLE clause is to generate random rows from a particular table, the TOP clause and the NEWID() system function can be used instead. Here’s a sample SELECT statement that will generate random rows from a table:
SELECT TOP 100 * FROM [dbo].[Customers] ORDER BY NEWID()
Here’s a link that discusses this method of generating random records from a table: Generate Random Records
|