question

Raj More avatar image
Raj More asked

Read sorted or sort during Clustered Index?

I am writing ETL that brings in about 50M rows into one of my tables and then creates a Clustered Index. Which option should I do: 1. Sort in source, insert sorted, create clustered index 2. Read from source unsorted, sort while creating clustered index in my system
ssissql-server-2008-r2etlclustered-indexsorting
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If you can insert the data sorted, I would create the clustered index first, then insert. But if you can't insert data sorted, you should create the clustered index later. Whichever is fastest is hard to tell, it depends on how fast you can sort the data in the source, how fast is your disk, how fast is your CPU(s), how fast and big is your RAM etc. Test the two scenarios with substantially smaller data sets. When you do insert, you might (probably will) benefit from splitting your inserts into smaller packages.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
As you mentioned that you insert sorted data first then create Index so I would recommend to create cluster index first then insert the sorted data. Or you can also try given below process- (1) Without sort data insert in table. (2) Create Clustered Index with option - with (SORT_IN_TEMPDB=ON).
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.