Index defaults 2

  • 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/

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

  • 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]

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

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

  • 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

  • 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

  • Hugo - nice question.

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

  • 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. 😉

  • 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!

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

  • Nice question, thanks.

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

  • nice question, I was also caught with #2 😀

  • 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 60 total)

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