Post reply

Use Cluster index or do not use it.

  • November 8, 2007 at 4:39 am

    #122198

    Hello,

    Can anyone tell me a good reason to i don't use or use cluster indexes?:blush:

    Can you give me one example of it?

    Thanks a lot.

    Regards,;)

    Jorge Mendes

  • November 8, 2007 at 5:16 am

    #749404

    It is understood that a clustered index is preferred for fileds without repeating values. For eg : if date is supposed to repeate in a table it is not a good candidate for clustered index.

    Fields supposed to be appeared in the where clause of queries can be used for non-clustered index. SQL Server 2005 added a new features 'include option' in the non-clustered index option.

  • November 8, 2007 at 5:17 am

    #749405

    Is there such a reason?

    you may want to read http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • November 8, 2007 at 5:21 am

    #749407

    Very generally speaking, the consensus is that having a ci is a 'good thing'.

    So, the 'starting assumption' is then - yes, you want them.

    Naturally, as with all 'rules' there are exceptions.

    Sometimes you don't want or need a clustered index.

    That is, however, more regarded as an exception.

    The summary then becomes; go with the 'probably a good choice', use clustered indexes, and revert if they prove to be a problem in some specific spot.

    /Kenneth

  • November 8, 2007 at 5:56 am

    #749415

    Hi,

    If you are not selecting a range or large result sets, do not require record set sorted or not grouping, then no real benefit of a clusterd index, but I suspect this is highly unlikely the case.

    Thanks,

    Phillip Cox

  • November 8, 2007 at 6:21 am

    #749424

    The balance between having a clustered index or not is complex, I published an anlysis of index size ( so having a clustered index is covered as part of the sizing )

    http://sqlblogcasts.com/blogs/grumpyolddba/archive/tags/Indexes/default.aspx

    The size matters posts shows a clustered index vs a heap structure for the same table - you may find it interesting.

    On the whole I'd say that for very small tables, typically under an extent, it may be ok but for larger tables you'd want a clustered index, if only to remove fragmentation.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 8, 2007 at 6:40 am

    #749433

    Thanks a lot everybody.

    The site and people that comes in are all very nice. Once more thanks a lot.

    😉

    Regards,

    Jorge Mendes

Viewing 7 posts - 1 through 6 (of 6 total)

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