Post reply

clustered index and non clustered index

  • January 28, 2009 at 1:44 pm

    #155196

    can any body please tell me ,at what time cluster index we create ,at what time non-clustered index we create

  • January 28, 2009 at 1:48 pm

    #934159

    I'm not sure what you're asking. Could you give more detail please?

    Are you asking for the reasons why we might create a clustered index or a nonclustered index?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 29, 2009 at 7:37 am

    #934678

    As far as "when" to create a clustered or nonclustered index, I'd strongly suggest creating the cluster first, then the noncluster. Since a clustered index key defines the storage for the data and that acts as a pointer to the data for the structure within the nonclustered index, if you create the nonclustered index on their own and then add a clustered index, it rebuilds the nonclustered index. That doesn't happen the other way around.

    Also, I strongly recommend that each table in a database should, with very few exceptions, have a clustered index, so this should be a fundamental part of the inital design of the tables.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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