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 ««12345»»»

Index defaults 1 Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 5:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:41 AM
Points: 907, Visits: 892
L' Eomot Inversé (6/14/2012)
Most of those errors were the same wrong answer, there appears to a widely distributed myth that any primary index is clustered unless it is declared as unclustered. I knew that such a myth existed, but I thought it was so often debunked that only a small proportion of people still believed it.

I don't think I'd call it a myth so much as an incomplete understanding of index creation pertaining to primary keys. Since the default is, in fact, a clustered index, many may never have considered the possibility that there was any other option. I know I hadn't. But I don't think I would describe my situation as tantamount to having mindlessly bought into a long-debunked ghost story that of course nobody in the modern age believes anymore.

ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1315787
Posted Thursday, June 14, 2012 6:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 2012 7:25 AM
Points: 298, Visits: 107
I would second that, as the first BOL quote says, a primary key IS created as clustered by default. There's just the "unless there's an additional unique constraint declared as clustered" line after that as well. Since we certainly all know you have to declare the clustered option before the comma that's how I (and the other 66% of responsers answering just "Clustered") tripped up.

Good question that proves you need to always pay FULL attention to the QotD question and answers!
Post #1315805
Posted Thursday, June 14, 2012 6:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 6:06 AM
Points: 763, Visits: 2,287
ronmoses (6/14/2012)
L' Eomot Inversé (6/14/2012)
Most of those errors were the same wrong answer, there appears to a widely distributed myth that any primary index is clustered unless it is declared as unclustered. I knew that such a myth existed, but I thought it was so often debunked that only a small proportion of people still believed it.

I don't think I'd call it a myth so much as an incomplete understanding of index creation pertaining to primary keys. Since the default is, in fact, a clustered index, many may never have considered the possibility that there was any other option. I know I hadn't. But I don't think I would describe my situation as tantamount to having mindlessly bought into a long-debunked ghost story that of course nobody in the modern age believes anymore.

ron


This.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1315813
Posted Thursday, June 14, 2012 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 1,422, Visits: 2,444
Excellent question, thanks Hugo!
There always seems to be a lot of confusion around this.
Post #1315816
Posted Thursday, June 14, 2012 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:57 PM
Points: 1,059, Visits: 1,794
ronmoses (6/14/2012)
L' Eomot Inversé (6/14/2012)
Most of those errors were the same wrong answer, there appears to a widely distributed myth that any primary index is clustered unless it is declared as unclustered. I knew that such a myth existed, but I thought it was so often debunked that only a small proportion of people still believed it.

I don't think I'd call it a myth so much as an incomplete understanding of index creation pertaining to primary keys. Since the default is, in fact, a clustered index, many may never have considered the possibility that there was any other option. I know I hadn't. But I don't think I would describe my situation as tantamount to having mindlessly bought into a long-debunked ghost story that of course nobody in the modern age believes anymore.

ron


If I may. I answered Clustered. The reason is that I was trying to divine the intent of the author. Is the intent to test your Index DEFAULT (as the name of the QotD implies), in which case the answer CLUSTERED is correct. The Default for PRIMARY KEY is CLUSTERED. In this case the Intent was to see if you knew the second half, which is... if CLUSTERED explicitly listed later in the Table Definition, does the PRIMARY KEY become CLUSTERED or NONCLUSTERED?

I submit that 86% are answering question 1, while the QotD is on Question 2. If it is the intent to test Question 2, then the question is poorly worded.
Post #1315825
Posted Thursday, June 14, 2012 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Okay, I got it wrong. I personally create constraints (other than PK and DEFAULT) separately from the CREATE TABLE statement, so when it says " execute a batch that contains only the CREATE TABLE statement" I didn't consider any other UNIQUE constraints would be created in the CREATE TABLE statement.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1315828
Posted Thursday, June 14, 2012 6:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
venoym (6/14/2012)

Is the intent to test your Index DEFAULT (as the name of the QotD implies), in which case the answer CLUSTERED is correct.


No, that's not correct.

The default for a primary key is a clustered index, UNLESS another clustered index is specified in the CREATE TABLE statement.
You just can't drop the sentence after "unless".
Logically, that would be the same as saying that

C = A ^ B
equals to
C = A




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 #1315839
Posted Thursday, June 14, 2012 7:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:07 AM
Points: 1,885, Visits: 375
Awesome question. Instinct was to pull the trigger on clustered, but then I made sure to read all the answers. So I tried to create the table and also add a column with a unique clustered constraint on it, and viola, the KeyColumn index changed to a nonclustered.

Learned something here, which is the point.
Post #1315842
Posted Thursday, June 14, 2012 7:09 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
venoym (6/14/2012)

If I may. I answered Clustered. The reason is that I was trying to divine the intent of the author. Is the intent to test your Index DEFAULT (as the name of the QotD implies), in which case the answer CLUSTERED is correct. The Default for PRIMARY KEY is CLUSTERED. In this case the Intent was to see if you knew the second half, which is... if CLUSTERED explicitly listed later in the Table Definition, does the PRIMARY KEY become CLUSTERED or NONCLUSTERED?

I submit that 86% are answering question 1, while the QotD is on Question 2. If it is the intent to test Question 2, then the question is poorly worded.


I certainly answered as though this were Question #1. Without being sure which answer the questioner was aiming at, it's perfectly conceivable that some portion of the 86% of us were all answering Question #1.

It's not as though I'm not aware of the fact that you can make the clustered index be something other than the Primary Key, just that when I want that, I generally create the table as a heap, with an ALTER TABLE ADD CONSTRAINT to add the Clustered index, followed by declaring the PK after that. It may not be the quickest way to do things, but when I'm looking at my code later, I can be sure I remember what my intentions were.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1315851
Posted Thursday, June 14, 2012 7:11 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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Jack Corbett (6/14/2012)
Okay, I got it wrong. I personally create constraints (other than PK and DEFAULT) separately from the CREATE TABLE statement, so when it says " execute a batch that contains only the CREATE TABLE statement" I didn't consider any other UNIQUE constraints would be created in the CREATE TABLE statement.


+1 Yay! At least I know I'm not alone now heh.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1315853
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse