THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The clustered index, the bulk insert and the sort operation

A few months ago I wrote about a SSIS setting you have to use just to get better performance when you (fast) load data into a table with a clustered key. I and Alberto met Stefano Stefani at SQL Server Conference 2007 (an Italian conference where we were all speakers) and we got the most direct Microsoft support service you can imagine, provided in our own native language, by one of the guy who writes the query optimizer.

The story is: when you have a clustered index, a bulk insert operation is really fast only when you insert data sorted with the same order as the clustered index expression. The same is true for fast load operations with SSIS (and DTS too). We noted that with an identity column used as clustered index, you don't get really fast insert. In this case, SQL Server doesn't realize that data are already sorted and it sorts them one more time.

Alberto explains very well this behavior in his new post. The bad news is that today you have to rely on some trick and/or workaround, as the dummy ORDER BY operation showed by Alberto. The good news is that a future CTP of Katmai will work without requiring these tricks.

Published Monday, July 2, 2007 11:31 PM by Marco Russo (SQLBI)



Emil Glownia said:

That is a good tip but again 'in his new post' doesn't seem to work.

November 25, 2011 12:06 PM

Marco Russo (SQLBI) said:

Thank you Emil, I just fixed the link!


November 25, 2011 12:13 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement