Post reply

Index defaults 2

  • June 27, 2012 at 4:25 am

    #1506050

    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>

  • June 27, 2012 at 5:47 am

    #1506067

    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

  • June 27, 2012 at 6:12 am

    #1506073

    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.

  • June 27, 2012 at 6:25 am

    #1506079

    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:

  • June 27, 2012 at 6:28 am

    #1506081

    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

  • June 27, 2012 at 6:28 am

    #1506082

    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! 🙂

  • June 27, 2012 at 7:05 am

    #1506102

    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

  • June 27, 2012 at 7:25 am

    #1506116

    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.

  • June 27, 2012 at 7:35 am

    #1506122

    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.

  • June 27, 2012 at 7:58 am

    #1506139

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

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

  • June 27, 2012 at 8:24 am

    #1506164

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

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

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

  • June 27, 2012 at 8:32 am

    #1506173

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

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

  • June 27, 2012 at 9:00 am

    #1506186

    Thanks for the question Hugo.

  • June 27, 2012 at 9:05 am

    #1506187

    +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

  • June 27, 2012 at 9:10 am

    #1506194

    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

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

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