Indexes?
-
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
-
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).
-
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.
-
I hope Joe's not gonna read this... we're not gonna hear the end of it.
-
Is that a dagger I see before me?
-
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.
-
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
-
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