[Fix] - Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE
Scenario :
- SELECT statements containing TABLESAMPLE generate this error message.
Error :
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.
Reason for the error :
- The TABLESAMPLE clause, presented in SQL Server 2005, restricts the number of rows fetched from a table in the FROM clause to either a sample number or to a PERCENT of rows.
Syntax - TABLESAMPLE:
TABLESAMPLE ( sample_number [PERCENT | ROWS] ) [ REPEATABLE (repeat_seed) ]
1. If ROWS is specified in the TABLESAMPLE clause, the sample_number must have an integer value as follows
SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 1000.50 ROWS )
- Again the command here will generate an error as follows:
- Msg 477, Level 15, State 0, Line 1
- Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.WikiTechy_Table".
2. The value or seed must be an integer. Hence modify the statement as shown below:
SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100.0 )
- Here again instead of integer, a decimal value is presented for REPEATABLE section. Hence the below error occurs:
- Msg 477, Level 15, State 0, Line 1
- Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE clause for table "dbo.WikiTechy_Table".
3. Finally to conclude, the value or seed must be an integer.
Fix :
- Ensure that the data delivered to the ROWS section or to the REPEATABLE section of the TABLESAMPLE clause is an integer value. Presented here the perfect sample for a TABLESAMPLE clause
SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 1000 ROWS )
SELECT * FROM [dbo].[WikiTechy_Table] TABLESAMPLE ( 50 ROWS ) REPEATABLE ( 100 )
Applies to sqlserver version :
Related Error Tags :
- SQL Server Performance Invalid ROWS value or REPEATABLE seed
- Invalid ROWS value or REPEATABLE seed “%I64d”
- Invalid ROWS value or REPEATABLE seed in the TABLESAMPLE
