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-29 : 10:23:59
|
| Greetings all,If I create a new table, should I create the clustered index BEFORE or AFTER I insert the data?! The size of the data varies as it could be a few thousand to a few hundred thousand.Your advice would be much appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 13:12:14
|
| If you are bulk inserting thousands of rows, it is better to add the indexes after you are done.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-05-30 : 06:37:45
|
quote:
Originally posted by tkizer
If you are bulk inserting thousands of rows, it is better to add the indexes after you are done.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
I'm usnig a a standards insert into select to populate my table. I'm just trying to make sense of how the database engine handles this. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-30 : 06:39:05
|
quote:
Originally posted by Abu-Dina
quote:
Originally posted by tkizer
If you are bulk inserting thousands of rows, it is better to add the indexes after you are done.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
I'm usnig a a standards insert into select to populate my table. I'm just trying to make sense of how the database engine handles this.
how many records does the select retrieve for you? |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-05-30 : 06:41:24
|
quote:
Originally posted by visakh16
quote:
Originally posted by Abu-Dina
quote:
Originally posted by tkizer
If you are bulk inserting thousands of rows, it is better to add the indexes after you are done.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
I'm usnig a a standards insert into select to populate my table. I'm just trying to make sense of how the database engine handles this.
how many records does the select retrieve for you?
As I said, it varies. Sometimes it inserts a few hundred, sometimes it's a few hundred thousands. Just wondering. Say I have a table made up of two columns (PersonId, Surname). If I create clustered index when the table is empty, is it best to put an ORDER BY clause in my insert statement? Would it make any difference?Thanks for any advice in advance. |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-05-30 : 10:51:33
|
| An order by won't do any good (assuming: insert into tab1 select cols from tab2 order by) as it's still going to insert into your index. What exactly are you trying to accomplish? Drop you indexes, insert into your table and rebuild your indexes, as Tara suggested. From a performance perspective, this would be the best way to proceed. If you are inserting a few hundred rows, I wouldn't even worry about the indexes (assuming you run some type of maintenance periodically). Again, what exactly are you trying to accomplish?Terry |
|
|
|
|
|
|
|
|
Clustered index question