Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2008-05-06 : 04:13:57
|
| Greetings all,What's best practice for creating clustered indexes?! Should they be added to a table AFTER it has been populated or should the clustered index be created BEFORE?Thanks for your advice in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-06 : 05:03:50
|
It depends.How many records are there in the table right now?How many records are to be inserted?What do you want to cluster by?How many columns are there in the table? What is the table layout?If records are inserted with ORDER BY, you can create the clustered index before, if the clustered index has some columns and same sort direction as the insert has.Also, an index is generally only created once. There are exceptions such as staging tables.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-05-06 : 05:12:00
|
| Hi Peso,I was always told never to answer a question with another question but for you Peso, the rules can be changed!On a serious note. I am looking at a script someone else has written. The script creates a temporary table, inserts about 30k rows then creates a clustered index. There is no order by clause in the insert statment and the clustered index is a compsite index made up of 2 columns. The table is only 4 columns wide.Hope this is enougn info |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-06 : 05:25:14
|
Oh, a temporary table.Create the table, insert the records and create the index. I think this will be faster thancreating the index first and the insert (even with order by).
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-05-06 : 05:33:27
|
| Thanks Peso, I shall leave that part of the script as it is. |
|
|
|
|
|
|
Creating clustered indexes