x

Hints in view definition

Can I use an option hint in my view definition to make it run faster?

more ▼

asked Dec 15, 2009 at 07:36 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Hints, sure... but not a query hint, as a view is just a stored SUB-query. It's a common misconception that a view is a stored query, and therefore should support things like an ORDER BY clause.

So, you can have table hints, such as NOLOCK, but not query hints such as RECOMPILE.

more ▼

answered Dec 15, 2009 at 07:39 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Although I agree with Rob's answer (and therefore voted for it), I want to clarify his point about ORDER BY. You can use an ORDER BY in a view definition if you also use the TOP clause. However, even then the exact order of results is not guaranteed. The ORDER BY will only be used to determine the top results. To sort those results, the query referencing the view would still need to specify its own ORDER BY clause.
Dec 15, 2009 at 07:48 PM Tom Staab
Yes, and with FOR XML too. But it's not the tradition usage of ORDER BY, and I hope will be replaced one day with TOP (N) OVER (ORDER BY...), and FOR XML OVER (ORDER BY...). Point well made though. I was definitely meaning for the ordering of results.
Dec 15, 2009 at 08:13 PM Rob Farley

Excellent answer. One hint I have used several times inside a view is noexpand. I talked about that in more detail at http://www.sqlservercentral.com/articles/Indexed+Views/63963/ .

And of course since you mention NOLOCK, it is worth pointing out that using that one runs the risk of dirty reads which can cause problems.
Aug 11, 2010 at 10:48 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Before you try to fix the slowness, you must understand the cause. Options are the last resort because they force the query optimizer to execute the query in a certain way. It might work for now but, if the data population changes, you're preventing the query optimizer from evolving a new plan. The chances are that there's a simple reason the query is slow so you should examine the query plan first. Look for the bits that show the highest percentage of the cost and also look at the parts where the lines are thickest as they are processing the most rows.

more ▼

answered Dec 16, 2009 at 05:41 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x249
x64

asked: Dec 15, 2009 at 07:36 PM

Seen: 4037 times

Last Updated: Dec 15, 2009 at 07:36 PM