Index defaults 2

Question

Index defaults 2

Viewing 15 posts - 16 through 30 (of 59 total)

You must be logged in to reply to this topic. Login to reply

call.copse SSCoach Points: 18064 More actions · Answer 1

I also went for answer 2 for the reasons as laid down by vk-kirov - I think in retrospect the wording 'what type of index (if any) will SQL Server make to support the FOREIGN KEY constraint' makes it pretty clear that seperately creating a unique constraint should not be considered as SQL creating a supporting index. So I guess I need to let Hugo off. <grumps>Still too much about the semantics though - bah!</grumps>

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Duncan Pryde (6/27/2012)
You can't define indexes explicitly (i.e. by CREATE INDEX) in a CREATE TABLE statement. As far as I know the only way to create indexes in CREATE TABLE statements is to have them created by defining primary keys or unique constraints.

Yes, but a UNIQUE constraint automagically adds an index so answer #2 could be a possbility. Or am I missing something here?

Thank you for the question, Hugo.

I love this kind of questions that makes you think twice, thrice.

Now I need some strong coffee. 😀

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

codebyo (6/27/2012)
Duncan Pryde (6/27/2012)
You can't define indexes explicitly (i.e. by CREATE INDEX) in a CREATE TABLE statement. As far as I know the only way to create indexes in CREATE TABLE statements is to have them created by defining primary keys or unique constraints.
Yes, but a UNIQUE constraint automagically adds an index so answer #2 could be a possbility. Or am I missing something here?
Thank you for the question, Hugo.
I love this kind of questions that makes you think twice, thrice.
Now I need some strong coffee. 😀

No, I thought the same too, but decided that it would be too much of a trick question if that were the answer. 😉 Also, Mike's question seemed to be about whether the fact that there was a foreign key constraint on the column meant that no index could be created.

I think the question was designed partly to check people didn't think that creating a foreign key also created an index - which is not an uncommon misconception.

sestell1 SSChampion Points: 10230 More actions · Answer 4

Toreador (6/27/2012)
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint
That's exactly the basis on which I answered "None unless the rest of the statement creates one", which I therefore believe should be the correct answer. Particularly given that the previous "index defaults" question was about the possibility of a Unique constraint appearing later in the statement!

Ugh, yea this tripped me up as well. My instincts were to pick "None", but after the last few QotD discussions on indexes I went for the second answer. :unsure:

Thomas Abraham SSChampion Points: 10761 More actions · Answer 5

I too went for the #2 choice. I knew that the no index would be created automatically, but thought ..... same as the others. At least I have lots of company.

Thanks for the question Hugo.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Dan Graveen Mr or Mrs. 500 Points: 525 More actions · Answer 6

I went for answer #2 as well, but then realized after the fact that the create index is indeed a seperate statement. So I learned a couple new things already today, the second being watch out for these close answers! 🙂

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

Duncan Pryde (6/27/2012)
I think the question was designed partly to check people didn't think that creating a foreign key also created an index - which is not an uncommon misconception.

Exactly my thoughts.

Even after getting the answer right I still have learned something new.

Excellent discussion and QotD!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

tim.bearne Ten Centuries Points: 1135 More actions · Answer 8

Question was "what type of index (if any) will SQL Server make to support the FOREIGN KEY constraint?". Answer is "none" and it's not possible to specify one in the rest of the statement. The fact that you can get an index made to support a completely different constraint is irrelevant.

Toreador SSChampion Points: 11368 More actions · Answer 9

tim.bearne (6/27/2012)
The fact that you can get an index made to support a completely different constraint is irrelevant.

No it's not. Just because it is created for one purpose doesn't mean that it can't achieve a different purpose as well. An index created to support a unique constraint will also support the foreign key constraint, even though that's not why it was created.

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 10

Oh... I see how it is. :rolleyes:

We put weird and misleading wording in answers #1 & #2 so people get it wrong.

EL Jerry SSCertifiable Points: 7063 More actions · Answer 11

Thank you for the question, Hugo. This is the kind of question that makes you think over and over again.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

Great question that really made me think. Probably should have added another assumption:

5. RefColumn is not unique in table dbo.QotD.

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

June 27, 2012 at 9:00 am

#1506186

Thanks for the question Hugo.

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 14

+1 for answer #2... FK won't create an index, but I got sucked into the trap overthinking the OTHER CONSTRAINTS.

Thanks for the question Hugo - cheers

seth delconte SSCertifiable Points: 6388 More actions · Answer 15

Toby Harman (6/26/2012)
You have two answers that are potentially correct here.
None, and None unless the rest of the command specifies one.
Shame I picked the wrong one!
Edit: I stand corrected. The CREATE INDEX has to be a separate command. Missed that.

Ditto to that!

_________________________________
seth delconte
http://sqlkeys.com