When it comes to loading data from one source system to another in SQL Server, there can be various challenges to overcome. One common problem is how to load data from a denormalized source into a normalized destination system. In this article, we will explore a solution to this problem that not only improves performance but also simplifies the coding process.

Let’s consider a scenario where we have a denormalized recordset of data that needs to be normalized into three tables: a “Client” table, an “Order Header” table, and an “Order Detail” table. The source data is provided without any usable primary or foreign key information, and the destination tables use integer surrogate keys generated using the SQL Server IDENTITY function.

The traditional approach to solving this problem involves using a cursor to loop through the denormalized source data and generate and apply surrogate keys. However, this approach can be verbose, hard to maintain, and time-consuming. In fact, it took 2 hours to process a million-row data source in one real-world example.

In order to improve performance and simplify the coding process, we can take a set-based approach. The first step is to add columns for the required surrogate IDs to the source data table. For each destination table, we find the last (greatest) ID and add IDs that increase monotonically according to the relational structure.

Once the source data has the required ID columns added, we can then update the IDs corresponding to the remaining destination tables. This is done by grouping the source data based on certain criteria and adding the IDs accordingly.

Finally, we can insert the data into the destination tables. It’s important to note that the data can be added in any order as long as there is no relational integrity in place on the destination tables. We must also remember to set IDENTITY_INSERT ON and OFF for each table.

So, what are the benefits of this set-based approach? Firstly, it significantly improves performance. In our example, the process that previously took 2 hours to complete now only takes 104 seconds, making it 69 times faster. Secondly, the code complexity and maintainability are greatly improved. The set-based approach requires fewer lines of code compared to the cursor-based approach. Lastly, this approach also uses fewer system resources.

While this set-based approach offers many advantages, there are a few potential challenges to consider. One potential issue is the risk of another process inserting data into the destination tables while the data set is being inserted. This could disrupt relational integrity. However, if you have control over the entire procedure, such as in a staging database or data preparation workflow, you can ensure that no other processes are running in parallel.

In conclusion, by adopting a set-based approach to loading data from a denormalized source into a normalized destination system, we can achieve significant performance improvements and simplify the coding process. This approach is particularly beneficial when dealing with large data sets. It’s important to carefully consider the potential challenges and ensure proper control over the data loading process to maintain relational integrity.