SQL Server Error Messages - Msg 494

Error Message

Server: Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

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 TABLESAMPLE clause cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot also be specified in the definition of a view or an inline table-valued function.

If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:

CREATE FUNCTION [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers]
        WHERE [ZIPCode] = @ZIPCode)
GO

SELECT * FROM [dbo].[ufn_GetCustomersByZIPCode] A TABLESAMPLE (100 ROWS)

Msg 494, Level 16, State 1, Line 3
The TABLESAMPLE clause can only be used with local tables.

Similarly, if the TABLESAMPLE clause is applied to a view (not a view definition), such as the following SELECT statement on a view, then this error message will also be generated.

CREATE VIEW [dbo].[AllStudents]
AS
SELECT [StudentID], [FirstName], [LastName]
FROM [dbo].[CurrentStudents]
UNION ALL
SELECT [StudentID], [FirstName], [LastName]
FROM [dbo].[PreviousStudents]
GO

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

Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

Solution / Work Around

If the purpose of using the TABLESAMPLE clause is to generate a random set of rows from a table, table-valued function or view, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used in its place.

In the case of the inline table-valued function shown earlier, the following SELECT statement can be used in its place which will generate 100 random rows from the rows returned by the inline table-valued function:

SELECT TOP 100 *
FROM [dbo].[ufn_GetCustomersByZIPCode] ( '12345' ) A
ORDER BY NEWID()

Similarly, in the case of the view, the following SELECT statement can be used in its place which replaces the TABLESAMPLE clause with the TOP clause together with the ORDER BY NEWID() clause:

SELECT TOP 100 * 
FROM [dbo].[AllStudents]
ORDER BY NEWID()