THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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 02, 2007 11:31 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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!

Marco

November 25, 2011 12:13 PM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement