SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexed Views


Indexed Views

Author
Message
Britt Cluff
Britt Cluff
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2343 Visits: 253
Learned something new today even though I missed the question. Thanks for submitting.

http://brittcluff.blogspot.com/
SathishK
SathishK
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 111
Thanks for the question and like the answer options very much :-)
SathishK
SathishK
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 111
sknox (2/10/2012)
Hugo Kornelis (2/10/2012)
I hate questions where I have to second-guess the author. Did the author mean that the optimizer would choose to use the indexed view automatically? Or did he mean that you could make the optimizer use it by using hints? I took a 50% chance gamble, and I chose wrong. Sad


If I'd meant automatically, I'd have said automatically. :-P


+1
SathishK
SathishK
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 111
sknox (2/10/2012)

BTW, now that I am posting anyway - there is one word in the explanation is wrong: "The query optimizer in lower editions will only consider the indexed view if you use the NOEXPAND table hint" (emphasis mine) - this is incorrect. With the NOEXPAND hint, the optimizer will not consider the indexed view; this hint forces the optimizer to use the indexed view. Enterprise edition (and developer edition) is the only place where the optimizer will consider the indexed view as one of multiple options, and choose the cheapest.


Yes, that is incorrect, but I think you've picked the wrong word(s) to correct. The intended meaning was "The query optimizer in lower editions will only consider the indexes on the view if you use the NOEXPAND table hint." The reason I used the word consider is that NOEXPAND does not force the use of a particular index, it only tells SQL server not to refer back to the original table.


Using NOEXPAND hint, says the optimizer to consider the index and it won't force its usage. To force a particular index usage, the optional INDEX() clause need to be used.

http://msdn.microsoft.com/en-us/library/ms181151.aspx
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18347 Visits: 12426
SathishK (2/10/2012)
Using NOEXPAND hint, says the optimizer to consider the index and it won't force its usage. To force a particular index usage, the optional INDEX() clause need to be used.

http://msdn.microsoft.com/en-us/library/ms181151.aspx

Using NOEXPAND means that the optimizer may not use any index on the underlying base table(s). The only freedom the optimizer has is to choose which of the indexes on the view to use. And I have never seen a real-world case where there was more than a single index on a view.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Lon-860191
Lon-860191
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 278
Good Question, I guessed right as I haven't worked wtih 2012 at all.
OzYbOi d(-_-)b
OzYbOi d(-_-)b
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 778
interesting question - tks
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64217 Visits: 18570
thanks for the question.

Hugo, thanks for the discussion points.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

chrisfradenburg
chrisfradenburg
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 2076
sknox (2/10/2012)
Hugo Kornelis (2/10/2012)
I hate questions where I have to second-guess the author. Did the author mean that the optimizer would choose to use the indexed view automatically? Or did he mean that you could make the optimizer use it by using hints? I took a 50% chance gamble, and I chose wrong. Sad


If I'd meant automatically, I'd have said automatically. :-P


You might do that. However, there have been several examples of questions where a detail like that was left out of the question. I would have liked the question more if it had been clear that the "automatically" was intentionally left out. I guessed at the meaning right but still got it wrong because I thought that indexed views weren't supported at all in Express, both by initial guess and after research. Which means that I learned something from from it so thank you. I'm looking forward to the article.
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4730 Visits: 2907
Hugo
And I have never seen a real-world case where there was more than a single index on a view


Does that mean that there couldn't be more than one? Does that also mean that the index hint will n ot work?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search