|
|
Error Message
Server: Msg 478, Level 16, State 0, Line 1
The TABLESAMPLE clause cannot be used in a
view definition or inline table function definition.
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 specified in the definition of a view or an inline table-valued function, this error message will be generated.
To illustrate on how this error message can be encountered, given the following table definition:
CREATE TABLE [dbo].[Customers] (
[CustomerID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[CustomerName] VARCHAR(100),
[Address] VARCHAR(100),
[City] VARCHAR(50),
[State] CHAR(2),
[ZIPCode] VARCHAR(5)
)
The following view definition, which uses the TABLESAMPLE clause, will generate the error:
CREATE VIEW [dbo].[RandomCustomers]
AS
SELECT *
FROM [dbo].[Customers] TABLESAMPLE (20 PERCENT)
GO
Msg 478, Level 16, State 0, Procedure RandomCustomers, Line 4
The TABLESAMPLE clause cannot be used in a view definition or inline table function definition.
Similarly, the following inline table-valued function will also generate the same error message:
CREATE FUNCTION [dbo].[ufn_GetRandomCustomers] (@ZIPCode VARCHAR(5), @SampleCount INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers] TABLESAMPLE (@SampleCount ROWS)
WHERE [ZIPCode] = @ZIPCode)
GO
Msg 478, Level 16, State 0, Procedure ufn_GetRandomCustomers, Line 5
The TABLESAMPLE clause cannot be used in a view definition or inline table function definition.
Solution / Work Around:
If the purpose of using the TABLESAMPLE clause in a view definition is to generate a random set of records from a given table, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used, as can be seen in the following view definition:
CREATE VIEW [dbo].[RandomCustomers]
AS
SELECT TOP 20 PERCENT *
FROM [dbo].[Customers]
ORDER BY NEWID()
GO
Similarly in the case of the inline table-valued function, instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used. But instead of a user-defined function, since the NEWID() system function is used, a stored procedure needs to be created in its place, as can be seen in the following stored procedure definition:
CREATE PROCEDURE [dbo].[usp_GetRandomCustomers]
@ZIPCode VARCHAR(5),
@SampleCount INT
AS
SELECT TOP (@SampleCount) *
FROM [dbo].[Customers]
WHERE [ZIPCode] = @ZIPCode
ORDER BY NEWID()
GO
Alternatively, since the inline table-valued function has been converted to a stored procedure, the TABLESAMPLE clause can now be used. Using the same parameters of the stored procedure shown above, the stored procedure will look as follows:
CREATE PROCEDURE [dbo].[usp_GetRandomCustomers]
@ZIPCode VARCHAR(5),
@SampleCount INT
AS
SELECT *
FROM [dbo].[Customers] TABLESAMPLE ( @SampleCount ROWS )
WHERE [ZIPCode] = @ZIPCode
GO
Unfortunately, executing this stored procedure passing the ZIP code and the sample count will generate a different error message:
EXECUTE [dbo].[usp_GetRandomCustomers] '12345', 10
Msg 497, Level 15, State 1, Procedure usp_GetRandomCustomers, Line 5
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.
As the error message suggests, variables, in this case the sample count parameter, are not allowed in the TABLESAMPLE clause. To overcome this limitation, the stored procedure can be modified to use dynamic SQL statement:
CREATE PROCEDURE [dbo].[usp_GetRandomCustomers]
@ZIPCode VARCHAR(5),
@SampleCount INT
AS
DECLARE @SQLStmt VARCHAR(100)
SET @SQLStmt = 'SELECT * FROM [dbo].[Customers]
TABLESAMPLE ( ' + CAST(@SampleCount AS VARCHAR(100)) + ' ROWS )
WHERE [ZIPCode] = ''' + @ZIPCode + ''''
EXECUTE ( @SQLStmt )
GO
|