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 1234»»»

View 7 Expand / Collapse
Author
Message
Posted Monday, May 7, 2012 9:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Comments posted to this topic are about the item View 7

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1296194
Posted Monday, May 7, 2012 11:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 13,251, Visits: 11,031
I picked no because of the COUNT_BIG requirement, but also because it is not known if ANSI_NULLS and QUOTED_IDENTIFIER were ON when the table was created. (and ANSI_NULLS when the view was created)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1296228
Posted Tuesday, May 8, 2012 12:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 597, Visits: 2,977
Wow, did a google search and there was no mention in any of the four posts I checked about the count_big so I got it wrong. Nice Q and nice reference article.


Post #1296247
Posted Tuesday, May 8, 2012 1:07 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
I have created numerous indexed views and answered numerous questions about them here on ssc as well, made this an easy question. Thanks!



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1296257
Posted Tuesday, May 8, 2012 1:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, October 11, 2014 4:03 AM
Points: 1,005, Visits: 988
Nice one, thank you
Iulian
Post #1296272
Posted Tuesday, May 8, 2012 2:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:07 AM
Points: 6,040, Visits: 8,322
dogramone (5/8/2012)
Wow, did a google search and there was no mention in any of the four posts I checked about the count_big so I got it wrong.

You would have found it if you checked Books Online: http://msdn.microsoft.com/en-us/library/ms191432.aspx. We all like to bash BOL when we find something missing or a documentation error, but in reality I still think SQL Server is one of the best documented programs on the market.

EDIT: Forgot to add: Nice question, but the code was a bit hard to read. May I suggest that the next time someone uses a screenshot for the question instead of just providing the code, he or she first increases the font size?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1296292
Posted Tuesday, May 8, 2012 2:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 4,016, Visits: 5,310
Good question, thanks Ron

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1296299
Posted Tuesday, May 8, 2012 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Interesting count & count_big. Learned something today. Thanks.
Post #1296435
Posted Tuesday, May 8, 2012 7:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 1,339, Visits: 1,715
Hugo Kornelis (5/8/2012)
dogramone (5/8/2012)
Wow, did a google search and there was no mention in any of the four posts I checked about the count_big so I got it wrong.

You would have found it if you checked Books Online: http://msdn.microsoft.com/en-us/library/ms191432.aspx. We all like to bash BOL when we find something missing or a documentation error, but in reality I still think SQL Server is one of the best documented programs on the market.

Agreed. AND BOL does have space for comments, so if it IS incorrect, we have the ability to fix it, rather than just complaining.


EDIT: Forgot to add: Nice question, but the code was a bit hard to read. May I suggest that the next time someone uses a screenshot for the question instead of just providing the code, he or she first increases the font size?

Agreed. They could also either 1) use PNG instead of JPG, or 2) increase the JPG quality setting of their screenshot app so there's not so much lossy compression.

The question itself was quite good, and the link provided in the explanation really does provide a good explanation of why this happens. (Essentially, SQL Server requires COUNT_BIG() on indexed views to prevent a potential overflow for tables with large numbers of rows.)

I got the question right because the existence of the question implied there was an issue, learned about the issue, and got a chance to comment! Win-win-win!
Post #1296460
Posted Tuesday, May 8, 2012 8:06 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: Thursday, October 16, 2014 11:48 AM
Points: 3,957, Visits: 3,644
Good question and explanation.
Post #1296477
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse