Creating a View With an Query Hint Option

[Richard Kroutil]

I want to create a View, that includes query hint Option (MAXDOP 1).
Does anyone know if that is possible or not ??? When you run the
script it does not like the OPTION part at all. If I remove the
Create View line, the script runs great. I know I can create a stored
proc to return the values but, I was curious after an hour of looking
around if anyone knew.

Here is the script:

Create View vw_tf_TestMyView
as
SELECT SVC00200.*, SVC00201.ITEMNMBR AS ITEMNUMBER,
SVC00201.PROBCDE AS PROBCODE,
SVC00201.CAUSECDE AS CAUSECODE,
SVC00201.RPRCODE AS REPAIRCODE,
ISNULL(SVC00901.DSCRIPTN, '') AS CAUSEDESCRIPTION,
ISNULL(SVC00907.DSCRIPTN, '') AS PROBLEMDESCRIPTION,
ISNULL(SVC00908.DSCRIPTN, '') AS REPAIRDESCRIPTION,
SVC00203.LINITMTYP AS LINITMTYPLINES,
SVC00203.TECHID AS TECHIDLINES,
SVC00203.ITEMNMBR AS ITEMNMBRLINES,
SVC00203.ITEMUSETYPE AS ITEMUSETYPELINES,
SVC00203.ITEMDESC AS ITEMDESCLINES,
SVC00203.LOCNCODE AS LOCNCODELINES,
SVC00203.UOFM AS UOFMLINES,
SVC00203.QTYORDER AS QTYORDERLINES,
SVC00203.QTYSOLD AS QTYSOLDLINES,
SVC00203.UNITCOST AS UNITCOSTLINES,
SVC00203.UNITPRCE AS UNITPRCELINES,
SVC00203.EXTDCOST AS EXTDCOSTLINES,
SVC00203.XTNDPRCE AS XTNDPRCELINES
FROM SVC00203 INNER JOIN
SVC00201 ON
SVC00203.SRVRECTYPE = SVC00201.SRVRECTYPE AND
SVC00203.CALLNBR = SVC00201.CALLNBR LEFT OUTER JOIN
SVC00908 ON
SVC00201.RPRCODE = SVC00908.RPRCODE LEFT OUTER JOIN
SVC00907 ON
SVC00201.PROBCDE = SVC00907.PROBCDE LEFT OUTER JOIN
SVC00901 ON
SVC00201.CAUSECDE = SVC00901.CAUSECDE RIGHT OUTER JOIN
SVC00200 ON
SVC00201.SRVRECTYPE = SVC00200.SRVRECTYPE AND
SVC00201.CALLNBR = SVC00200.CALLNBR

UNION ALL
SELECT svc30200.*, svc30201.ITEMNMBR AS ITEMNUMBER,
svc30201.PROBCDE AS PROBCODE,
svc30201.CAUSECDE AS CAUSECODE,
svc30201.RPRCODE AS REPAIRCODE,
ISNULL(SVC00901.DSCRIPTN, '') AS CAUSEDESCRIPTION,
ISNULL(SVC00907.DSCRIPTN, '') AS PROBLEMDESCRIPTION,
ISNULL(SVC00908.DSCRIPTN, '') AS REPAIRDESCRIPTION,
svc30203.LINITMTYP AS LINITMTYPLINES,
svc30203.TECHID AS TECHIDLINES,
svc30203.ITEMNMBR AS ITEMNMBRLINES,
svc30203.ITEMUSETYPE AS ITEMUSETYPELINES,
svc30203.ITEMDESC AS ITEMDESCLINES,
svc30203.LOCNCODE AS LOCNCODELINES,
svc30203.UOFM AS UOFMLINES,
svc30203.QTYORDER AS QTYORDERLINES,
svc30203.QTYSOLD AS QTYSOLDLINES,
svc30203.UNITCOST AS UNITCOSTLINES,
svc30203.UNITPRCE AS UNITPRCELINES,
svc30203.EXTDCOST AS EXTDCOSTLINES,
svc30203.XTNDPRCE AS XTNDPRCELINES
FROM svc30203 INNER JOIN
svc30201 ON
svc30203.SRVRECTYPE = svc30201.SRVRECTYPE AND
svc30203.CALLNBR = svc30201.CALLNBR LEFT OUTER JOIN
SVC00908 ON
svc30201.RPRCODE = SVC00908.RPRCODE LEFT OUTER JOIN
SVC00907 ON
svc30201.PROBCDE = SVC00907.PROBCDE LEFT OUTER JOIN
SVC00901 ON
svc30201.CAUSECDE = SVC00901.CAUSECDE RIGHT OUTER JOIN
svc30200 ON
svc30201.SRVRECTYPE = svc30200.SRVRECTYPE AND
svc30201.CALLNBR = svc30200.CALLNBR
OPTION (MAXDOP 1)

[Itzik Ben-Gan]

It seems that OPTION hints are not allowed in a CREATE VIEW statement.
I'm not sure if it's any consolation, but you can specify such hints in a query against the view:

SELECT ...
FROM V1
OPTION(MAXDOP 1)

--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com

"Richard Kroutil" <rkro...@rwkroutil-inc.com> wrote in message news:572018a1.03042...@posting.google.com...

[Bill Hollinshead [MSFT]]

Hi Richard,

Itzik is correct. This designed limitation isn't very clear in BOL, but
this design is based up the following exerpt:
{
Only one OPTION clause may be specified with the statement. The query hint
affects all operators in the statement. If a UNION is involved in the main
query, only the last query involving a UNION operator can have the OPTION
clause.
}
You can consider a view to be equivalent to a sub-select, and thus a view
is not an object that would be a complete statement by itself (agreed -
this is obtuse <g>). Thus the above restriction applies to views.

The use of MAXDOP implies (to me <g>) that you may be attempting to work
around an optimizer bug. If so, please let me know the bug or KB article
number, your DDL and your DML - perhaps we can optimize in a different
manner. Another workaround would be to set degrees of parallelism (at the
server level) to 1. Many have discovered this server setting does not
noticeably affect server-wide performance. Even when degrees of parallelism
is set to 1, your SMP box will still have all of SQL Server's worker
threads scheduled upon all available processors. Such thread scheduling is
the major reason for SQL Server's use of those processors. If there is some
reason (e.g., slower server-wide performance) that precludes you from
setting degrees of parallelism to 1, and if other approaches have been
discounted (e.g., indexing and a fullscan update of statistics), then
perhaps it would be best to open a support case (we refund support charges
when a bug has been experienced). That support case can be used to request
a HotFix (this not always possible <g>, although the KB does indicate we
have fixed the vast majority of such bugs) or the support case can be used
to seek a more detailed analysis (than could perhaps be done via newsgroups
<g>) of this problem (with the goal being to obtain a more acceptable
workaround).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.