Index defaults 1

  • Thanks for the question Hugo, and more thanks for your inevitably thorough follow-up explanations and replies to objections.

    I got it wrong in the "best way possible" which for me is just fine: like others who've replied, I usually explicitly create constraints and keys after table creation.

    To my mind, this kind of behavior is akin to the default length of 1 when declaring a VARCHAR() without explicitly setting a length. If I keep to a standardized way of doing things explicitly, I don't have to remember obscure exceptions to expected behavior.

    Rich

  • Thanks Hugo. A challenging question that really got me thinking.

    I initially got my back up over its wording but, after reading your reasons behind the question, I have come around to your way of thinking 🙂

  • Any answer can be valid as anything unspecified can follow - any alter table, drop table, even drop database.

    It's like a question "Guess what I'm thinking about".

  • Robert-378556 (6/22/2012)


    Any answer can be valid as anything unspecified can follow - any alter table, drop table, even drop database.

    It's like a question "Guess what I'm thinking about".

    "If I execute a batch that contains only the CREATE TABLE statement"

    Implies that there is only the create statement. We were shown part of the statement.

    For what its worth I answered incorrectly, selecting the clustered index. But then again I miss too many questions to be terribly bothered by one more red x 😉

  • Robert-378556 (6/22/2012)


    Any answer can be valid as anything unspecified can follow - any alter table, drop table, even drop database.

    It's like a question "Guess what I'm thinking about".

    That's why I explicitly included that I execute a batch that only includes the CREATE TABLE statement. That rules out ALTER TABLE, DROP TABLE, DROP DATABASE, and a whole bunch of other options.


    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/

  • If we are not mention any index for primary key , then Clustered index created for Primary key (by default). In this question, primary key created with out any specification, So Clustered index is the right answer, correct?

  • subrammail (6/27/2012)


    If we are not mention any index for primary key , then Clustered index created for Primary key (by default). In this question, primary key created with out any specification, So Clustered index is the right answer, correct?

    Almost correct, but not completely.

    If the CREATE TABLE also includes a UNIQUE constraint, and the declaration of that UNIQUE constraint includes the CLUSTERED keyword to override the default kind of index for a UNIQUE constraint, then the index for the primary key will default to nonclustered (because there can only be one clustered index).


    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/

  • Great question!

Viewing 8 posts - 46 through 52 (of 52 total)

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