| Author |
Topic |
|
govthamb
Starting Member
27 Posts |
 Posted - 2009-12-02 : 01:34:11
|
| Hi,I have a table with around 50 million records. Daily inserts and updates will occur on the table regularly as well as select.Col1 is identity and no indexes are available at present. I would like to know if we need to create clustered index or non-clustered index on this col1. Will the performance be improved with index on this col1?Please suggest.Thanks |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-12-02 : 01:37:30
|
| Weather do you have any primary key?? IF no set the Identity column as Primary key(clustered index).Else set the Identity column as non-clustered index.It will speed up your select query if you utilize the indexes.Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-02 : 01:44:12
|
| If you do have a primary key on the identity column, then you already have an index on it. The default for an index on a primary key is clustered.And yes it will speed up the performance of queries if you are using the identity value in your queries. Otherwise, you should consider putting the clustered index on a different column or columns.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
govthamb
Starting Member
27 Posts |
Posted - 2009-12-02 : 01:45:49
|
| I have created a clustered index on the col1.I created non-clustered indexes in other 3 columns of the table.Performance is good in select but while inserting or updating the records the performance is very slow. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
govthamb
Starting Member
27 Posts |
Posted - 2009-12-02 : 01:54:54
|
| Thanks Tara and senthil..Please suggest me how can I confirm this is a hardaware related issue. I mean any commands which gives me know with numbers to analyse this as hardware problem, So that I will try to suggest for better hardware.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
govthamb
Starting Member
27 Posts |
Posted - 2009-12-02 : 01:59:14
|
| Thanks a lot Tara. I will check the same. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Index on identity column