Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Index defaults 2 Expand / Collapse
Author
Message
Posted Wednesday, June 27, 2012 10:28 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:57 AM
Points: 542, Visits: 569
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.
Post #1322020
Posted Wednesday, June 27, 2012 11:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:40 AM
Points: 1,717, Visits: 469
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...
Post #1322048
Posted Wednesday, June 27, 2012 4:11 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 723, Visits: 2,123
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

Cheers
Roddy
Post #1322198
Posted Wednesday, June 27, 2012 4:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,916, Visits: 8,167
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
Post #1322208
Posted Wednesday, June 27, 2012 8:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:34 AM
Points: 897, Visits: 1,486
All I have to say is: keep those questions coming.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1322247
Posted Wednesday, June 27, 2012 10:11 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 4,383, Visits: 3,393
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.
Post #1322254
Posted Wednesday, June 27, 2012 10:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 4,383, Visits: 3,393
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.
Post #1322255
Posted Wednesday, June 27, 2012 10:14 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 4,383, Visits: 3,393
Oh my.. that's what happens if you do Reply instead of Edit, after a 15 hour workday...

Sorry.
Post #1322256
Posted Thursday, June 28, 2012 7:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
got something new !!!



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1322436
Posted Thursday, June 28, 2012 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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...
Post #1322438
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse