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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-02-23 : 14:01:44
|
| I've got a ~ 20GB table that I need to do some work on (indexes, fill factor change, etc). Clearly, I can't do it online, since it will block an entire web app for god knows how long.What I'd like to do is copy the table's structure and data to a new table that has the proper indexes and fill factory already defined. What's the fastest way to do that, and is there any way to do that as a non-logged operation? DTS? BCP? Something else?I'm after performance, because this will need to be done a couple of times a year, and I'd prefer it to be non-logged just to lighten the load on the server and to save disk space.Thanks-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-23 : 14:09:00
|
| -BCP out the data. Native format is both faster and smaller (-n flag)-Create a new, empty table-DO NOT create any non-clustered indexes on the new table-You CAN create a clustered index on the new table, but it MUST match the index used in the original table-Use the TABLOCK and ORDER hints when you bcp in the data to the new table. See Books Online for the details-Build the remaining indexes after the data is loaded-Consider a separate filegroup for the non-clustered indexes on the new tableLook in Books Online for more details, there is an entry or two on optimizing bulk insert performance, it covers all the above and more. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-02-23 : 14:49:46
|
| Thanks!-b |
|
|
|
|
|
|
Fastest way to copy big table?