Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Hugo Kornelis » Index defaults 2
Add to briefcase
60 posts, Page 2 of 6
««12345»»»

Index defaults 2 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
vk-kirov
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: Today @ 3:16 AM
Points: 3,189, Visits: 4,143
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
Gazareth
Posted Wednesday, June 27, 2012 3:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:53 AM
Points: 1,474, Visits: 2,342
Nice question, thanks.
Post #1321750
Mike Hays
Posted Wednesday, June 27, 2012 3:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:02 AM
Points: 300, Visits: 307
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
imarran
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: Tuesday, April 09, 2013 4:15 AM
Points: 309, Visits: 86
nice question, I was also caught with #2
Post #1321762
Duncan Pryde
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: Today @ 2:51 AM
Points: 3,046, Visits: 1,301
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
call.copse
Posted Wednesday, June 27, 2012 4:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 1,084, Visits: 690
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
codebyo
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, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436
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
Duncan Pryde
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: Today @ 2:51 AM
Points: 3,046, Visits: 1,301
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
sestell1
Posted Wednesday, June 27, 2012 6:25 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: Yesterday @ 12:27 PM
Points: 831, Visits: 1,203
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
Thomas Abraham
Posted Wednesday, June 27, 2012 6:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,147, Visits: 1,449
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.
Post #1321820
« Prev Topic | Next Topic »

Add to briefcase
60 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.