Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexed Views In Non-Enterprise Editions of SQL Server


Indexed Views In Non-Enterprise Editions of SQL Server

Author
Message
sknox
sknox
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 2709
Comments posted to this topic are about the item Indexed Views In Non-Enterprise Editions of SQL Server
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11546
Thanks for this article, sknox. It's disconcerning to see that, apparently, such grave misconceptions about indexed views exist.

In the introduction, you write that someone told you that NOEXPAND is not availabla as of SQL Server 2008R2. I don't know who wrote that and where he/she got this information, but I can tell you that this is completely wrong (trying hard to avoid stronger language here). The NOEXPAND hint is fully supported in all versions, including SQL Server 2008R2 and SQL Server 2012. This hint is also not documented as being deprecated, so there are no plans to remove support in a future edition. And to the best of my knowledge, there are no plans to deprecate this hint in a future version.

I also want to provide some feedback on a few minor points that I noticed in your article.

At one point, you write: "the percentages aren't definitive because of the random nature of the data I'm using" - I just wanted to point out that the percentages are NOT a measure of the actual cost of queries. The percentages shown in the execution plan are always based on the estimated execution plan (even if you are running the query with the option to include the actual execution plan). You can see some of the differences between estimates and reality by hovering your mouse over various parts of the execution plan, but neither total cost of an iterator or entire query, nor query cost relative to the batch support that - the most important measures that you can get an actual vs estimate on are row counts for all of the data streams (arrows), and both row counts and numbers of executions for iterators.
The percentages on the execution plan are often a good measure, but the caveats are that (1) if the optimizer miscalculates because of incorrect assumptions or outdated stats, you won't see it; and (2) they are based on query cost, which is based on assumptions that might no hold for you (e.g. when your database is already in cache - the query cost calculation is based on the assumption that most I/O will be non-cached; or when elapsed time is the only thing that's important to you, even at the cost of increased I/O; or ......)
When I compare perfomance of queries, I always use SET STATISTICS TIME to check elapsed time (or CPU time if that's what I want to minimize), and SET STATISTICS IO to check how much I/O the query used. Or I use extra code to put the datetime at the start of the query in a variable, then calculate the DATEDIFF in milliseconds at the end of the query and return it to the client or save it in a table for later analysis. (The latter method is very useful when I let the computer run overnight to run lots of long-running tests, or to repeat the same test multiple time to average out random spikes).

In your conclusion, you mention some conditions where an indexed view will not be used. This is absolutely true for queries with no hint running on Enterprise Edition. But when you use the NOEXPAND hint, then (in ANY edition!) SQL Server no longer has any option - the term hint is extremely misleading; it is the same kind of hint parents give to their children to clean up their rooms: "you might want to ..." - but if you don't, they'll just ground you for a week. If you use the NOEXPAND hint, the optimizer WILL choose a plan that uses at least one of the indexes defined on the view - even if other plans would have been cheaper.


Finally - I just saw the link to where you found the misinformation about the NOEXPAND hint. I tried to comment on the article, but I was forced to log in to some site I've never heard of and I am still contemplating if this is worth getting yet another account for. I also checked a few other articles on that site by the same author, and I can only conclude that this man is dangerous. He has no idea what he's talking about, and yet dares to write in an authorative tone, happily spreading misinformation all over the world. Please, whenever you rean an article by him, double check other sources before assuming that he's right.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Michal Sz.
Michal Sz.
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
NOEXPAND hint is also available in SQL server 2005 (including express edition). I use it in my applications and works very well.
jay1b
jay1b
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 50
I discovered this a few months back after an SQL Server Trainer told me they were only in Enterprise edition. Running nicely on my website running 2008 Express.

When using the indexed view, you can avoid having to put with (noexpand) throughout your stored procedures/code by wrapping the indexed view in a normal view.

The example taken from a database running on SQL 2000 Standard.


CREATE VIEW [dbo].[vw_Country_idx] WITH SCHEMABINDING
AS
SELECT cg_ct_id, cg_id, c_id, cg_name, c_Name, c_shortcode
FROM dbo.country_group
INNER JOIN dbo.country_group_list ON cg_id = cgl_cg_id AND (cgl_Archived = 0 OR cgl_Archived IS NULL)
INNER JOIN dbo.country ON c_id = cgl_c_id AND (c_Archived = 0 OR c_Archived IS NULL)
WHERE (cg_Archived = 0 OR cg_Archived IS NULL)
GO

CREATE UNIQUE CLUSTERED INDEX [idx_vw_Country] ON [dbo].[vw_Country_idx]
( [c_id] ASC, [cg_ct_id] ASC)
GO

CREATE VIEW [dbo].[vw_Country]
AS
SELECT *
FROM vw_Country_idx WITH (NOEXPAND)
GO
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
In fairness, http://msdn.microsoft.com/en-us/library/cc645993.aspx for 2012 does show that "Direct query of indexed views (using NOEXPAND hint)" (in the RDBMS Manageability section) is not available in Express editions (though it certainly is). The 2008 R2 version (http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx) suggests at least Standard is required (i.e. not Web, Workgroup or Express) though again it does work on all 2008 R2 versions. The 2008 version (http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx) doesn't list NOEXPAND explicitly at all.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Joffrey V
Joffrey V
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 195
Thanks for this. I thought it was only available in Enterprise Edition. ;-)

===============================
= Ignorance is the choice not to know =
===============================
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