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 ««12345»»»

Index defaults 2 Expand / Collapse
Author
Message
Posted Wednesday, June 27, 2012 3:11 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Koen Verbeeck (6/27/2012)
I doubt a 1-1 foreign key relationship is very useful

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).
Post #1321746
Posted Wednesday, June 27, 2012 3:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 1,855, Visits: 2,965
Nice question, thanks.
Post #1321750
Posted Wednesday, June 27, 2012 3:47 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
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.
Post #1321760
Posted Wednesday, June 27, 2012 3:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 317, Visits: 98
nice question, I was also caught with #2
Post #1321762
Posted Wednesday, June 27, 2012 4:07 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: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
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.
Post #1321773
Posted Wednesday, June 27, 2012 4:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,618, Visits: 1,064
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>
Post #1321780
Posted Wednesday, June 27, 2012 5:47 AM


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
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
Post #1321803
Posted Wednesday, June 27, 2012 6:12 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: Thursday, July 17, 2014 4:47 AM
Points: 3,352, Visits: 1,480
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.
Post #1321810
Posted Wednesday, June 27, 2012 6:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 1,276, Visits: 2,197
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.
Post #1321818
Posted Wednesday, June 27, 2012 6:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 1,760, Visits: 2,136
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
Post #1321820
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse