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 in it’s minimal locking. Prior to merge, we had to update the record if it existed. And if it did not exist, we inserted the record. Long story short, it was double the amount of locking before the optimizer knew which rows to exclusively lock. To understand what’s going on in the statement below, you have to read starting from the MERGE statement. This is where the initial updating is taking place first. As you scroll down, you will see the OUTPUT clause. This actually outputs the results of the MERGE statment. You can think of this output as a virtual table named “merged”. The $action variable is an intrinsic column (if you will), that contains the action that row played during the MERGE. Lastly, if you scroll to the top, you will find the insert statement that finally inserts the new “current” record for the corresponding “expired” record we just retired. This was the same row we used for the criteria to UPDATE the expired row, however we did not use any of it’s values. [cc lang=”sql”] DECLARE @Now datetime = GETDATE() DECLARE @EffToDate datetime = ‘2079-06-06T00:00:00.000’ DECLARE @JobID int = 1 — This […]

Continue reading ...

Using the Merge Statement’s OUTPUT Clause to Match Inserted IDENTITY Fields with Source Table Fields

With this project I needed to insert data into table1 from table2 while simultaneously retrieving the identity field of table1, then insert that identity into table3 which mapped to table2 on a key that table1 did not have. The OUTPUT clause when used with the input statment would not facilitate this because it could not return the identity and the source key in the same row. The result was to use the SQL Server 2008 Merge statement with the output clause. Traditionally I would have looped the insert one record at a time and returned back both the values into variables, then inserted the identity into table3 by joining the keys on table2 and table 3. [cc lang=”sql”] CREATE TABLE #MatchIDs ( Table_1_EmployeeID int, Table_2_EmployeeID int ) INSERT INTO #MatchIDs ( Table_1_EmployeeID, Table_2_EmployeeID ) SELECT mrg.Table_1_EmployeeID ,mrg.Table_2_EmployeeID FROM( MERGE dbo.Table_1 t1 USING dbo.Table_2 t2 ON 1 = 0 WHEN NOT MATCHED THEN INSERT ( FirstName ) VALUES ( t2.FirstName ) OUTPUT $action ,t2.EmployeeID ,t1.EmployeeID ) AS mrg ( output_action ,Table_1_EmployeeID ,Table_2_EmployeeID ) WHERE mrg.output_action = ‘INSERT’ [/cc] The result is a statement that actually fools the MERGE by intentionally not matching any rows and just inserting all the records into the table.

Continue reading ...

SQL Server Slow Performance

This post deals with a random hanging that sometimes happens with SQL Server 2005+. In order to troubleshoot SQL Server Slowness, go here. The introduction of the new SQL Server 2005 Query Optimization engine has brought great things (including statement-level caching and smarter execution plan generation). There is however a little more overhead with the advent of this new technology. Aside from taking longer to generate an execution plan, I have noticed two separate instances where a query would appear to intermittently hang. From a database engine perspective however, the query is not hanging but generating an execution plan. The two instances I’ve witnessed this in were in both SQL Server 2005 (sp2), and also now in SQL Server 2008. Both procedures were relatively small, however somewhat complex in their where clauses. The physical indicators in both instances where very high CPU usage and very low IO usage for one particular SPID which we gathered by executing sp_who2. Here is an example of the query that hung on SQL Server 2008: [cc lang=”sql”] SET @IsTrue = ( SELECT COUNT(1) FROM dbo.table1 t1 JOIN dbo.table2 t2 ON t1.ColumnID = t2.ColumnID WHERE ( t2.ID = @ID AND t2.SomeDate < GETDATE() ) OR EXISTS ( SELECT 1 FROM dbo.Table3 WHERE ID = @ID AND AlternateID IN (1,2,3,4,6) ) ) [/cc] Once the query would hang in generating the execution plan, the effect seemed to snowball and cause other executing instances of the same procedure to hang. Also, no blocking was occuring in the […]

Continue reading ...
  1. Pages:
  3. 1
  4. 2
  5. 3

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php