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 |
|
Antonio
Posting Yak Master
168 Posts |
 Posted - 2006-10-16 : 12:20:22
|
| Greetings SQL friends,This is probably a silly question but which is the preferred method for creating indexes?! Should it happen after the table is populated or before?I had a discussion about indexes a while back and I remember getting confused about clustered indexes and I find myself confused yet again!If I create a clustered index on an empty table then proceed to populate the table, how does SQL Server sort the records?! What if I populate the table and then create my index, doesn’t that create a better clustered index than the first instance?Thanks for your answers in advance._________________________________________________________________________________________________________________________Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-16 : 12:55:07
|
| I think my ideal would be:Create clustered indexBulk load the data pre-sorted same order as Clustered Index (AND provide the HINT to alert SQL Server that the import is pre-sorted)Create IndexesCreate Foreign KeysKristen |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Antonio
Posting Yak Master
168 Posts |
Posted - 2006-10-17 : 05:20:30
|
quote:
Originally posted by Kristen
I think my ideal would be:Create clustered indexBulk load the data pre-sorted same order as Clustered Index (AND provide the HINT to alert SQL Server that the import is pre-sorted)
Brett's answer is confusing me now. So he reckons the loads (data retrieval) will be faster if the index is created AFTER the table is populated?! Why?The issue I have to deal with is estimating the size of the table as I don't know whether the file group allocated for indexes is big enough.I guess I will have to do a rough estimate of the reduction in size of my aggregate table and scale it up to decide which filegroup I should use in the live environment._________________________________________________________________________________________________________________________Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-17 : 10:04:58
|
| A clustered index sorts the actual data in the table acording to the order of columns in the index so if you manage to insert sorted data instead of unsorted you'll be in pretty good shape either way. I would think that adding the clustered index after the insert would give increased overall performance but it's hard to tell (I haven't tested). If however the data is unsorted I'd probably do this in 3 steps:1. Insert the data2. Create the clustered index (this will probably cause alot of page splitting because alot of data will be moved around)2. Do a rebuild of the index (look up DBCC DBREINDEX or ALTER INDEX in BOL)--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-17 : 10:26:13
|
quote:
Originally posted by AntonioSo he reckons the loads (data retrieval) will be faster if the index is created AFTER the table is populated?! Why?
Loading data is not a data retreival process...it is a....well a load.If you have an empty table, and it has no indexes on it, then you do a bcp in, or BULK INSERT of a file that has a significant size, you will notice a difference in speed.However, you will still need to build the indexes after the fact.I prefer to do it this way, because it simplify's each process.MOOBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-17 : 13:36:41
|
| "Brett's answer is confusing me now."Hes saying the same thing as me- with the possible exception of any Clustered Index.I suggest you try it each way and see which performs better in your circumstances.If you cannot pre-sort your import data the Clustered Index is irrelevant - and you would be better to create that too after the Bulk Load - but it will be "shot" until your next reindex run, as Lumbago has pointed out.Kristen |
|
|
|
|
|
|
|
|
Index before or after