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 9:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 371, Visits: 955
Got it right because I know creating an FK doesn't create and index and you can't specify such a thing in the syntax. It has been odd lately that I've been getting these Jedi mind trick questions right and at times I get the simple ones wrong just because I didn't read is close enough.

Cheers
Post #1321950
Posted Wednesday, June 27, 2012 9:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 21,297, Visits: 14,988
Thanks Hugo.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1321954
Posted Wednesday, June 27, 2012 9:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 371, Visits: 955
Toreador (6/27/2012)
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.


But that wasn't the point of the question.


Cheers
Post #1321955
Posted Wednesday, June 27, 2012 9:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:28 AM
Points: 1,714, Visits: 6,256
jfogel (6/27/2012)
[quote][b]
But that wasn't the point of the question.


I agree now I know the answer.
But it could have been - it was the point of the previous question in this series so why not this one too?
Post #1321958
Posted Wednesday, June 27, 2012 9:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 371, Visits: 955
Toreador (6/27/2012)
jfogel (6/27/2012)
[quote][b]
But that wasn't the point of the question.


I agree now I know the answer.
But it could have been - it was the point of the previous question in this series so why not this one too?


Mind games!


Cheers
Post #1321983
Posted Wednesday, June 27, 2012 9:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
Koen Verbeeck (6/27/2012)
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint:

CREATE TABLE dbo.QotD
(KeyColumn int NOT NULL PRIMARY KEY,
RefColumn int NOT NULL,
-- More column definitions
CONSTRAINT FK_RefTab FOREIGN KEY (RefColumn)
REFERENCES dbo.RefTab(RefTabKey),
-- More constraints
CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)
);

Doesn't it count as answer #2?


It's possible, but I doubt a 1-1 foreign key relationship is very useful

I also took answer number 2. Forgot that you cannot specify an index directly in a create table statement. D'oh!



I knew you couldn't create the index, but a 1-1 cardinality does occasionally exist, so a Uniqueness constraint specified to help the FK should be a valid way to get a Uniqueness index onto a table in the create statement.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1321999
Posted Wednesday, June 27, 2012 9:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1322002
Posted Wednesday, June 27, 2012 9:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
jfogel (6/27/2012)
Toreador (6/27/2012)
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.


But that wasn't the point of the question.


How do you know that for certain when you answer it? If #2 had been correct and the answer had stated that you could create a Uniqueness constraint and get a Unique Index created that would also support the foreign key, then a bunch of other folks would be crying foul.

Sorry Hugo, normally I enjoy your questions, and when I get them wrong, I still learn something. Knowing that the answer could be considered to be either #1 or #2 depending is just a little upsetting.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1322005
Posted Wednesday, June 27, 2012 10:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:49 PM
Points: 371, Visits: 955
If you are asking me, my point is that I answered the question based on how it was put. It didn't ask anything other than what type of index (if any) would be added based on the syntax given. All I can say is that this time I saw through a question that could have easily tripped me up. It just didn't this time. Earlier this week there was a question where I completely agreed it was confusing and suggested a "explain your answer" area would be appropriate for it. Maybe for this one too?

Cheers
Post #1322011
Posted Wednesday, June 27, 2012 10:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:17 AM
Points: 400, Visits: 977
I wanted desperately to pick # 2 but I couldn't figure out how to add that index before the ). YES!

I liked the question.
Post #1322017
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse