SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index defaults 2


Index defaults 2

Author
Message
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3732 Visits: 4408
Koen Verbeeck (6/27/2012)
I doubt a 1-1 foreign key relationship is very useful :-D

At current job I deal with some billing software. There's a table of payments containing such rows as PAYMENT_ID (primary key), date of payment, amount of money, currency code etc. Any payment may come from a limited number of sources (cash, bank check, ATM etc). Each source of payment define its unique (and rare used) set of payment attributes (transaction number, cash register identifier etc).
So there are several tables for supporting these attributes (say CASH_PAYMENT_SPEC, BANK_PAYMENT_SPEC, ATM_PAYMENT_SPEC). Each table has a primary key containing one column (PAYMENT_ID) and a foreign key constraint that references the dbo.PAYMENT.PAYMENT_ID column (i.e., those foreign keys just implement 1-1 relationships).
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 5843
Nice question, thanks.
Mike Hays
Mike Hays
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 764
Hey Hugo, would you explain to me why "None, unless the rest of the statement specifies one" is wrong? Are you saying that the statement can not have an index on that column because it already has a foreign key constraint? This not intended to be mean, just would like a little clarification.
imarran
imarran
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 116
nice question, I was also caught with #2 :-D
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 1552
Mike Hays (6/27/2012)
Hey Hugo, would you explain to me why "None, unless the rest of the statement specifies one" is wrong? Are you saying that the statement can not have an index on that column because it already has a foreign key constraint? This not intended to be mean, just would like a little clarification.


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.
call.copse
call.copse
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3885 Visits: 1927
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
Andre Guerreiro
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1515
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. :-D

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 1552
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. :-D


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
sestell1
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2561 Visits: 3464
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
Thomas Abraham
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2761 Visits: 2255
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.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
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