Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Indexed View Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 11:37 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
Comments posted to this topic are about the item Indexed View
Post #830442
Posted Monday, December 7, 2009 11:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:17 AM
Points: 21,213, Visits: 14,911
The wording in the explanation don't seem to match the documentation.
http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx


Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #830443
Posted Tuesday, December 8, 2009 12:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
Hi,

Thanks a lot,

CirquedeSQLeil (12/7/2009)
The wording in the explanation don't seem to match the documentation.
http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx


Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.


Actually the explanation should be like "When you create an indexed view, the view definition must not contain any non- deterministic functions".

However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.
Post #830458
Posted Tuesday, December 8, 2009 2:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
mohd.nizamuddin (12/8/2009)

However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): http://msdn.microsoft.com/en-us/library/ms178091.aspx
All of the aggregate and string built-in functions are deterministic.
Post #830491
Posted Tuesday, December 8, 2009 3:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 23, 2010 2:15 AM
Points: 7, Visits: 3
Exactly, aggregate functions are deterministic. So they are allowed in indexed view. "Correct" answer is incorrect!

Deterministic and Nondeterministic Functions:
http://msdn.microsoft.com/en-us/library/ms178091.aspx
Post #830518
Posted Tuesday, December 8, 2009 5:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
vk-kirov (12/8/2009)
mohd.nizamuddin (12/8/2009)

However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): http://msdn.microsoft.com/en-us/library/ms178091.aspx
All of the aggregate and string built-in functions are deterministic.


Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.


/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #830566
Posted Tuesday, December 8, 2009 6:38 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
hakan.winther (12/8/2009)
vk-kirov (12/8/2009)
mohd.nizamuddin (12/8/2009)

However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

This is incorrect.
All of the aggregate and string built-in functions are deterministic.


Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.

I know this What I only said is: "aggregate functions are deterministic", nothing about indexed views
Post #830606
Posted Tuesday, December 8, 2009 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:30 AM
Points: 33,062, Visits: 15,177
Explanation corrected.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #830660
Posted Tuesday, December 8, 2009 7:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 8:35 AM
Points: 1,608, Visits: 198
Wasn't sure about CharIndex since it can return a null value and which could invalidate the index statement for the view. Has anyone ever used CharIndex on an indexed view?

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
Post #830668
Posted Tuesday, December 8, 2009 8:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:25 AM
Points: 1,710, Visits: 721
I tried an example containing a charindex column in SQL2005, and while it did create the index on the view, the following warning was shown when it was created.

Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression.

The execution plan for queries using the view show the use of indexes on the underlying tables, rather than the index on the view.

So, while the system allows you to create an index on a view containing a charindex column, it doesn't appear to be of any use.
Post #830735
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse