SQL Server Best Practices Article

Published: September 11, 2006
Writers: Sanjay Mishra
Technical Reviewers:  Lubor Kollar, Tom Davidson, Prem Mehra, Kevin Cox

Loading bulk data into tables is one of the most common tasks in data warehouse applications. As the data volume increases, so does the complexity of the loading process. While loading bulk data, you should not only consider how much time it takes to load the data, but also consider the concurrency and data availability requirements, and the maintenance operations, such as creating or rebuilding indexes. Bulk data loading can take a significant amount of time. Therefore, you should consider the options that enable fast loading of data. However, loading fast is not enough. Loading bulk data can, at times, cause escalation of locks and impact other concurrent transactions on the table. For applications requiring concurrent data access during load, you should consider options that provide the desired concurrency. Availability of existing data while the new data is being loaded is also an important consideration to help you choose the right option. Creating and rebuilding indexes is an essential part of the data loading process, but can be time-consuming for large tables. However, there are ways to create indexes on the freshly loaded data without impacting the data in the rest of the table.SQL Server 2005 provides partitioned tables, and while loading data into a partitioned table, you can have several options. Each option provides its own distinct advantages, but the option you choose depends upon the requirements of your application, such as performance, concurrency, temporary storage, and so on. This document presents the data loading options on a partitioned table and compares them in terms of performance, concurrency and temporary storage requirements.