Post reply

Index defaults 2

  • June 26, 2012 at 8:12 pm

    #260501

    Comments posted to this topic are about the item Index defaults 2

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • June 26, 2012 at 8:14 pm

    #1505937

    You have two answers that are potentially correct here.

    None, and None unless the rest of the command specifies one.

    Shame I picked the wrong one!

    Edit: I stand corrected. The CREATE INDEX has to be a separate command. Missed that.

  • June 26, 2012 at 9:24 pm

    #1505944

    Nice question .. learned something

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 26, 2012 at 10:03 pm

    #1505949

    Thanks, Hugo -- a nice one to end my day with!

  • June 26, 2012 at 11:43 pm

    #1505969

    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? 🙂

  • June 27, 2012 at 12:29 am

    #1505991

    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!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 27, 2012 at 1:03 am

    #1505993

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

    Thanks - I was wondering why my answer was incorrect too. I thought it strange that Hugo might have made a mistake!

    Steve Hall
    Linkedin
    Blog Site

  • June 27, 2012 at 1:53 am

    #1506001

    Hugo - nice question.

    Surprised only 15% have this correct so far...

  • June 27, 2012 at 1:58 am

    #1506003

    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? 🙂

    That's exactly what I thought - but then decided that given this was a question from Hugo Kornelis it wouldn't be that sneaky, so went for option 1.

    Thanks for the question. 😉

  • June 27, 2012 at 2:14 am

    #1506007

    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!

  • June 27, 2012 at 3:11 am

    #1506018

    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).

  • June 27, 2012 at 3:28 am

    #1506022

    Nice question, thanks.

  • June 27, 2012 at 3:47 am

    #1506031

    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.

  • June 27, 2012 at 3:49 am

    #1506033

    nice question, I was also caught with #2 😀

  • June 27, 2012 at 4:07 am

    #1506044

    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.

Viewing 15 posts - 1 through 15 (of 59 total)

You must be logged in to reply to this topic. Login to reply