This article could just as well be called creating a historical snapshot table. This type of table is also referenced as a dimension depending on what kind of data repository it’s located in. Personally, I prefer to keep a historical snapshot table in a normalized data store that contains history. This normalized data store is [...]
Archive of posts filed under the Data Warehouse category.
Extracting Data from a Source System to History Tables
This is a topic I haven’t found much information written about, however nearly every system I’ve worked with needs this exact functionality. It is important that the method for extracting data be done in a way that does not hinder performance of the source system. In this example, the goal is to extract data from [...]
SQL Server 2008 Minimally Logged Inserts
SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the [...]
Quick Table Transfers (Imports) using SSIS, Bulk Insert or BCP
Ever wonder why sometimes data transfer can be lightning fast while other times you’re watching sp_who2 wondering when it’s going to finish? It’s likely you’re noticing the difference between minimal logging and full logging. Even in a simple recovery model for a database you can experience row inserts to both the transaction log and the [...]
Using the Merge Statement to populate a historical table with Effective Dating
One of my favorite uses for the MERGE statement introduced in SQL Server 2008 is the updating of a historical table. With versions prior to 2008 this operation had to be performed in two separate statements. Merge helps us to streamline the process. The advantage to the database engine when using a Merge statement is [...]
Transferring Large Amounts of Data using Batch Inserts
Below is a technique used to transfer a large amount of records from one table to another. This scales pretty well for a couple reasons. First, this will not fill up the entire log prior to committing the transaction. Rather, it will populate the table in chunks of 10,000 records. Second, it’s generally much quicker. [...]
