June 13, 2012 at 8:47 pm
Comments posted to this topic are about the item Index defaults 1
June 14, 2012 at 12:31 am
Great question - thanks.
Does make you think before answering, especially given the amount of information in the question.
June 14, 2012 at 12:36 am
Great question Hugo.
This is a statement that supports the answer:
CREATE TABLE dbo.QotD2
(KeyColumn INT NOT NULL PRIMARY KEY
,Test INT NOT NULL
,CONSTRAINT [UX_Test] UNIQUE CLUSTERED
(
Test ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
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 1:06 am
Great question.
Hugo, you are the number ONE!
Thanks.
😀
June 14, 2012 at 1:42 am
This was removed by the editor as SPAM
June 14, 2012 at 2:21 am
I don't think the answer is correct for the question (mentioed in screen shot).Because if you write like this , it will create
a key that is treated as Primary Key and index wise it is clustered index
June 14, 2012 at 2:26 am
amit_adarsh (6/14/2012)
I don't think the answer is correct for the question (mentioed in screen shot).Because if you write like this , it will createa key that is treated as Primary Key and index wise it is clustered index
Please read the question carefully:
Below you see a part of a CREATE TABLE statement.
-- More column and constraint definitions below
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 3:04 am
Koen Verbeeck (6/14/2012)
amit_adarsh (6/14/2012)
I don't think the answer is correct for the question (mentioed in screen shot).Because if you write like this , it will createa key that is treated as Primary Key and index wise it is clustered index
Please read the question carefully:
Below you see a part of a CREATE TABLE statement.
-- More column and constraint definitions below
I missed that, d'oh what a douchebag, good question Hugo i'll look out for yours in future and spend a bit more time reading them 😛
June 14, 2012 at 4:56 am
Very good question.
The error rate so far (86% - 241 out of 280) is amazing. 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. Well, that was clearly wrong - even though some of that 86% probably arises from carelessness in reading the question what is left after allowing for that carelessness is not a small proportion!
Tom
June 14, 2012 at 5:56 am
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
June 14, 2012 at 6:31 am
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!
June 14, 2012 at 6:40 am
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.
June 14, 2012 at 6:42 am
Excellent question, thanks Hugo!
There always seems to be a lot of confusion around this.
June 14, 2012 at 6:48 am
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.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply