CODEWITHSUNDEEP
sqlsql-serversql-server-2000
kayhan yüksel
kayhan yüksel

Reputation: 378

sql server 2000 using hint in view

CREATE VIEW xxxx AS
SELECT order_date, 
       DATEPART(W, order_date) AS dayID, 
       Type 
  FROM dbo.Shipment_Dates 
 WHERE (TIP = 'normal') 
OPTION (FAST 20)

This create statment causes error at hint part .. Is there a workaround for adding hint to views?

Upvotes: 3

Views: 1149

Answers (2)

gbn
gbn

Reputation: 432271

You can't use query (OPTION) hints in a view.

A view is simply a macro that expands. The query hint would then be internal in the expansion because the view is then a subquery. And query hints are not allowed in subqueries.

Effectively, you'd have this:

select
   *
from
   (
   SELECT
     order_date, DATEPART(W, order_date) AS dayID, Type 
   FROM dbo.Shipment_Dates 
   WHERE (TIP = 'normal') 

   option (fast 20)  --not allowed
   ) WasAView

From MSDN:

Query hints can be specified only in the top-level query, not in subqueries.

Upvotes: 4

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Instead of specifying the hint in the definition of the view, specify the hint when using the view:

select order_date, dayID, Type
    from xxxx
    option (fast 20)

Upvotes: 3

Related Questions