The TABLESAMPLE clause cannot be used in a view definition or inline table function definition.

Error Message:
Msg 478, Level 16, State 0, Procedure MyOrder, Line 5
The TABLESAMPLE clause cannot be used in a view definition or inline table function definition.

Severity level:
16.

Description:
This error message appears when you try to use the TABLESAMPLE clause inside a view or inline table function.

Consequences:
The SQL statement cannot be parsed and further execution is stopped.

Resolution:
Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. The TABLESAMPLE clause must be removed.

Versions:
This error message was introduced with SQL Server 2005.

Example(s):
USE tempdb;
CREATE VIEW dbo.MyOrder
AS
SELECT *
  FROM Northwind.dbo.Orders
  TABLESAMPLE (10 ROWS)
GO

CREATE FUNCTION dbo.udfMyFunc()
RETURNS TABLE
AS
RETURN
(SELECT OrderID, AVG(DATEDIFF(DAY, OrderDate, ShippedDate) * 1.0) AS Days_till_delievery
   FROM Northwind.dbo.Orders
  GROUP BY OrderID
  TABLESAMPLE (10 ROWS)
);
GO

Remarks:
In the above example we try to use the TABLESAMPLE clause inside a view. Tis raises the error.

]]>

Leave a comment

Your email address will not be published.