June 14, 2012 at 6:49 am
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
Consultant - Straight Path Solutions
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
June 14, 2012 at 6:58 am
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2012 at 7:02 am
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.
June 14, 2012 at 7:09 am
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.
June 14, 2012 at 7:11 am
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.
June 14, 2012 at 7:23 am
There' a way too common misbelief that a primary key is always clustered, end of story. I just didn't realise it was this common.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2012 at 7:49 am
I'm not agree.
With the TSQL evidence given (just " a part of " the command) the default behaviur is to assume a Clustered index.
For me is a tricky question.
June 14, 2012 at 7:53 am
Great question Hugo.
Put me in the camp of answering too quickly. I guess I didn't consider adding a clustered index on another column. DOH!!! I read the section on Index defaults and didn't read the intention of the questions very carefully. I should have known that Hugo would not ask such a simple and straight forward question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 8:10 am
Tricky, but good question...thanks Hugo!
June 14, 2012 at 8:13 am
Ric Sierra (6/14/2012)
I'm not agree.With the TSQL evidence given (just " a part of " the command) the default behaviur is to assume a Clustered index.
The whole point of the question is that with only part of the command you cannot assume anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2012 at 8:44 am
Good question. I fell for it. But I don't think we should make too many assumptions about how common the clustered primary key myth is with knowing how many people either incorrectly guessed the intent of the question or just didn't consider that there may be a clustered constraint in the rest of the table definition.
John
June 14, 2012 at 8:54 am
Nice question Hugo! You got me on this. I didn't think it through. Considering the failure rate on this question, I would say that you enlightened a few people with this one. Good job!
June 14, 2012 at 9:18 am
GilaMonster (6/14/2012)
Ric Sierra (6/14/2012)
I'm not agree.With the TSQL evidence given (just " a part of " the command) the default behaviur is to assume a Clustered index.
The whole point of the question is that with only part of the command you cannot assume anything.
Then we are assuming that the author didn't intend to test us the default of the Primary Key?
From the description, emphasis mine. If you want a technicality, both CLUSTERED and the "Depends" answers are correct given the same information. This question was not testing the myth of Always Clustered on Primary Key, it was testing whether or not we think that Hugo would put a "Depends" answer or if he would go with what BOL calls the Default for a PRIMARY KEY statement. Sadly, I don't know Hugo well enough to make that assertion ahead of time, and I was left with an ambiguous answer choice. Like you said, with only part of the statement it's not safe to assume which question he is testing us on.
SQL Server will always create an index to support a PRIMARY KEY constraint. This index can be either clustered or nonclustered. You can specify this with the optional CLUSTERED or NONCLUSTERED keyword, but that has to come before the comma, so in the given statement, it is not possible to specify the index type. When the index type is not given, it defaults to clustered, except when the table also contains a UNIQUE constraint with CLUSTERED specified.
June 14, 2012 at 9:26 am
Thanks, everyone, for the feedback! Both the compliments and the constructive feedback are all very welcome.
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.
You are not the only one with this or a similar objection, but you are the first to put it this clear, so I have chosen your message to respond to.
The intent of my question is on par with the title of the question. It's about index defaults. In this case, the rules for what index is created by default for a primary key. And that rule is a little more involved than a simple "clustered" - it includes being aware of the possibility of a unique constraint with clustered index in the same table, which influences the default.
Your statement ("if the intent is to test the default, then clustered is correct") is incorrect. In the Books Online topic on CREATE TABLE (see the link in the question's explanation), the first statement about default index type does appear to support your statement: "PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED". But immediately below that follows a second sentence that adds more information about the default: "If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED". Add these sentences together, and the short form is that, in a CREATE TABLE statement, the default index type for a primary key is "clustered, unless a unique index is specified with the clustered keyword". That is what I wanted to test, and I am not surprised that a majority of people got it wrong (though I had hoped that the number of correct answers would be higher). I am also delighted to see that of those answering wrong, the vast majority at least picked the "least incorrect" answer, and that the correct answer comes out ahead of all the other distractors.
There are also a few people who didn't consider the possibility of unique constraints being defined in the same CREATE TABLE statement, for instance because they have the habit of using explicit ALTER TABLE statements for that. I just hope that these people don't feel tricked. I did not want to make this a trick question. I even explicitly added a comment "-- More column and constraint definitions follow" (emphasis added here), to make it extra clear that the reader should assume that the create table statement can containt more constraints than just the primary key. If anyone knows how I could have made this even more clear, do let me know; I always try to learn and improve my question writing skills.
June 14, 2012 at 9:31 am
Great question Hugo. Cheers.
GilaMonster (6/14/2012)
There' a way too common misbelief that a primary key is always clustered, end of story.
<--- + 1, been there, debated this too. :hehe:
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply