Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohd Nizamuddin » Indexed View
Add to briefcase
16 posts, Page 1 of 2
12»»

Indexed View Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mohd.nizamuddin
Posted Monday, December 07, 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
SQLRNNR
Posted Monday, December 07, 2009 11:40 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329
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 2008

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
mohd.nizamuddin
Posted Tuesday, December 08, 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
vk-kirov
Posted Tuesday, December 08, 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: 2 days ago @ 1:32 AM
Points: 3,187, Visits: 4,140
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
ser_s
Posted Tuesday, December 08, 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
hakan.winther
Posted Tuesday, December 08, 2009 5:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438
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
Post #830566
vk-kirov
Posted Tuesday, December 08, 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: 2 days ago @ 1:32 AM
Points: 3,187, Visits: 4,140
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
Steve Jones - SSC Editor
Posted Tuesday, December 08, 2009 7:24 AM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406, Visits: 13,722
Explanation corrected.






Follow me on Twitter: @way0utwest

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 03, 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
Mike Mullen
Posted Tuesday, December 08, 2009 8:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:28 PM
Points: 1,409, Visits: 675
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
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.