Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index defaults 2


Index defaults 2

Author
Message
Mike Hays
Mike Hays
Right there with Babe
Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)

Group: General Forum Members
Points: 719 Visits: 761
mtassin (6/27/2012)
Thomas Abraham (6/27/2012)
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.


I've been burned too many times by "and the rest of the create statement is syntactically correct" followed by "well you could have this or that in the rest of that statement so you're wrong".

I hate Semantics, I also hate trying to read somebody's mind when they wrote the question.



I am in agreement with statement above. By including Answer #2 as one of the possibilities, the question went from "does SQL create an index with a foreign key" to "guess whats in the code not shown."

When I get the answer wrong because I do not have that knowledge, then it is a learning opportunity for me. When I get a question wrong because it is a play on word, then I am just left disappointed.

It is a good questions, just an "opps" on the answer.
Michael Riemer
Michael Riemer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2694 Visits: 626
Seems I can only Ditto all the quotes on the second answer! Got me I guess

Thanks for the great question Hugo. Strange not to see any replies from you in the discussions though... ;-)
Roddy.CAMERON
Roddy.CAMERON
SSC Eights!
SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)SSC Eights! (898 reputation)

Group: General Forum Members
Points: 898 Visits: 2529
To be honest I thought the question was quite clear. It asked what index SQLSERVER created to support the foreign key. It did not ask what other constraints, which may create indexes on the same column, could YOU add to the rest of the create statement which would also by chance support the FK.

SQLServer does not create any indexes to support FK's, you must do it yourself, so the only possible answer was "None". I disagree with people talking about the question being a play on words or having to read Hugo's mind. It wasn't. I agree you needed to read the question carefully and think about it but heck, it's a question from Hugo so that's just a given :-D

Cheers
Roddy
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8327 Visits: 11580
Sorry for my lack of replies. I am currently attending the TechEd Europe conference in Amsterdam, so I have very little time for other stuff.

I don't have time to address all individual responses (I have to get out of bed in about 5 hours, and I'm far from ready to get in it), so I'll just try to cover the general feedback with a few sentences.

First: Thanks to everyone who had kind words about this questions, or about me or my questions in general. Much appreciated!

Second: It was never my intention to make this a wordplay or mindguessing game. I tried to cover all the possible unintended interpretations in the question. But I have to admit that I did overlook one.
Having a UNIQUE constraint on a FOREIGN KEY column is rare - but not impossible. The index created for the unique constraint will also support the foreign key. That was not what I wanted to test, though; my goal for this question was to test if people know that just declaring a foreign key does not automatically create a supporting index.
If I had considered this possible interpretation in advance, I would probably have added a small selection of sample data or so, or I would just have added a note that there is nu unique constraint on the foreign key column.

My apologies to all who picked the wrong answer because of this oversight. And also my apologies to everyone who felt tricked or deceived by my question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 1514
All I have to say is: keep those questions coming. :-D

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 Visits: 4718
Hugo Kornelis (6/27/2012)
Sorry for my lack of replies. I am currently attending the TechEd Europe conference in Amsterdam, so I have very little time for other stuff.

I don't have time to address all individual responses (I have to get out of bed in about 5 hours, and I'm far from ready to get in it), so I'll just try to cover the general feedback with a few sentences.

First: Thanks to everyone who had kind words about this questions, or about me or my questions in general. Much appreciated!

Second: It was never my intention to make this a wordplay or mindguessing game. I tried to cover all the possible unintended interpretations in the question. But I have to admit that I did overlook one.
Having a UNIQUE constraint on a FOREIGN KEY column is rare - but not impossible. The index created for the unique constraint will also support the foreign key. That was not what I wanted to test, though; my goal for this question was to test if people know that just declaring a foreign key does not automatically create a supporting index.
If I had considered this possible interpretation in advance, I would probably have added a small selection of sample data or so, or I would just have added a note that there is nu unique constraint on the foreign key column.

My apologies to all who picked the wrong answer because of this oversight. And also my apologies to everyone who felt tricked or deceived by my question.

Hugo, this is how the world works today: no matter what you do, there is always somebody against it.

Please keep them coming, no matter whether I score a point or not. I always learned someting valuable from your posts, correct answer or not.

IMO, that's what counts.

Pl;ease say HI! to de Wallen for me, if you will.
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 Visits: 4718
Revenant (6/27/2012)
Hugo Kornelis (6/27/2012)
Sorry for my lack of replies. I am currently attending the TechEd Europe conference in Amsterdam, so I have very little time for other stuff.

I don't have time to address all individual responses (I have to get out of bed in about 5 hours, and I'm far from ready to get in it), so I'll just try to cover the general feedback with a few sentences.

First: Thanks to everyone who had kind words about this questions, or about me or my questions in general. Much appreciated!

Second: It was never my intention to make this a wordplay or mindguessing game. I tried to cover all the possible unintended interpretations in the question. But I have to admit that I did overlook one.
Having a UNIQUE constraint on a FOREIGN KEY column is rare - but not impossible. The index created for the unique constraint will also support the foreign key. That was not what I wanted to test, though; my goal for this question was to test if people know that just declaring a foreign key does not automatically create a supporting index.
If I had considered this possible interpretation in advance, I would probably have added a small selection of sample data or so, or I would just have added a note that there is nu unique constraint on the foreign key column.

My apologies to all who picked the wrong answer because of this oversight. And also my apologies to everyone who felt tricked or deceived by my question.

Hugo, this is how the world works today: no matter what you do, there is always somebody against it.

Please keep them coming, no matter whether I score a point or not. I always learned someting valuable from your posts. IMO, that's what counts.

Please say Hi! to de Wallen for me, if you will.

Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 Visits: 4718
Oh my.. that's what happens if you do Reply instead of Edit, after a 15 hour workday...

Sorry. Ermm
Danny Ocean
Danny Ocean
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 1549
got something new !!!

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
roger.plowman
roger.plowman
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 1125
While I'm late to the party and missed answering yesterday :-) I knew foreign keys don't generate indexes, but my question is why the heck not?

Foreign keys are there because you're going to join the data--and efficient joins require indexes. Therefore under what conditions would you *not* want a foreign key to be indexed? Shouldn't SQL Server's create statement index by default using a similar naming convention that it uses for other default names?

Or failing that, at least have an index option on the foreign key creation code? On by default, but allowing it to be overridden in the rare case you really don't want an index?

The more I work with SQL Server the more I get the feeling it's intended as a "manual transmission", it does NOTHING unless you explicitly tell it.

Me, I prefer an automatic that knows to do a few things itself...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search