Post reply

Indexes?

  • June 13, 2005 at 6:59 pm

    #109969

    Hello All,

    As i told you guy,I am new Sql DBA and at my work their is a performance issue.Today i find out couple of table is not having clustered index on primary key,but those tables has unique indexes on primary key and some other indexes.

    I am planing to create clustered index on primary key of those table.

    Can i create clustered index on existing tables loaded with data and have non clustered indexes.

    would you recommend to create to create clustered index on primary key.

     

    Any other suggestion to find out performance issue.

    Thanks

    Narinder

  • June 13, 2005 at 8:08 pm

    #565156

    It's always a good idea to have a clustered index. Make sure you don't just make a clustered index for the fun ot it. Be certain that it's the best columns for that index (columns frequntly used in where conditions, especially between operations).

  • June 14, 2005 at 1:35 am

    #565183

    If you put a clustered index on something it is best done on a field that is naturally ordered such as a sequential id field.

    LinkedIn Profile

  • June 14, 2005 at 6:46 am

    #565258

    I hope Joe's not gonna read this... we're not gonna hear the end of it.

  • June 14, 2005 at 6:53 am

    #565265

    Is that a dagger I see before me?

    LinkedIn Profile

  • June 14, 2005 at 7:02 am

    #565272

    Joe HATES identity columns like the pest, now you wanna create the clustered index on it ???

    I'm not gonna restart that discussion here. Andy, just make sure that you choose the best index for the table.

  • June 15, 2005 at 1:44 am

    #565565

    Hi

    Before you create a clustered index, allways ask yourself -

    1 - oltp or olap ?

    2 - are there many range lookups?

    3 - how big (datatype) is the column that i'm gonna index

    4 - is it unique by nature

    It can be beneficial to create a clustered index on an identity col (often primary key), but sometimes it much more beneficial to crate a clustered index on the column that is always seached in range lookups. Creating clustered indexes on large datatypes is mostly not the best idea.

    JP

  • June 15, 2005 at 8:58 am

    #565727

    JP is right.  You need to understand why are you creating the index.  If it is just because that the table doesn't have a clustered index, then maybe don't create it.  What benefit are you going get from creating this index.  Every index that you add will cost you disk space and additional time to insert/update/delete records. 

    Also don't forget that when you create a new clustered index (CI) on a table that has nonclustred indexes, all of the nonclustered indexes (NCI) will be rebuilt.  This could take a long time to recreate all of the NCIs.  Do you have a tiime window to do this? 

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

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