Technical: Microsoft – SQL Server – Transact SQL – Compare Insert-Update Combination VS Merge Statement – Output Variables

Technical: Microsoft – SQL Server – Transact SQL – Compare Insert-Update Combination VS Merge Statement – Output Variables

Background

After all these days with SQL Server Transact SQL, just now found out about a magical sleight of hands with Transact SQL.

Scenario

Let us say we want to perform an action and capture the results of the SQL Statement.

In our case, we are updating data and we want to return the result of our update to the calling script.

Here  is a Stored Procedure


create procedure dbo.usp_AddData
(
     @qualifier1  bigint
   , @delta       int
   , @columnValue money = null output
 
)
as
begin
    update [tableSales]
    set    [column1] =  [column1] + @delta
    where  [qualifier] = @qualifier1

    select @columnValue = [column1]
    from   [tableSales]
    where  [qualifier] = @qualifier1

end

 

In the dbo.usp_AddData Stored Procedure (SP) we are updating the tableSales table. Specifically the column named column1.

In the second SQL Statement, we are retrieving the result of our earlier update statement.


Here  is the Stored Procedure Invocation


declare @qualifier1   bigint
declare @delta        int
declare @columnValue  money

exec  dbo.usp_AddData
          @qualifier1  = @qualifier1
        , @delta = delta
        , @columnValue = null output

 

 

Aggregate Data and read new value

I accepted the fact that I have to update a table and retrieve the new result for years.  And, then yesterday I was going over the psssql archive and found a noteworthy post:

Reduce locking and other needs when updating data – Better Performance
http://blogs.msdn.com/b/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx

 

The gem is that we can accomplish the update and read of new value within a single statement:

declare @iVal int
update CounterTable 
set    @iVal = iVal = iVal + 1 
where  CounterName = 'CaseNumber'

return @iVal

Our Use Case

DDL – Create Tables

Here we create our tables – dbo.salesOutputDate & salesOuputDataYear.

  • dbo.salesOutputData – detailed table
  • dbo.salesOutputDataYear – aggregate table that contains summary sales data for each Sales Person per year

use [DBLab] go set noexec off go /* drop table dbo.salesOutputData drop table dbo.salesOutputDataYear */ go if OBJECT_ID('dbo.salesOutputData') is not null begin set noexec on end go create table dbo.salesOutputData ( [salesPersonID] bigint not null , [salesDate] datetime not null , [salesAmount] money not null , constraint PK_SalesOutputData primary key ( [salesPersonID] , [salesDate] ) ) go set noexec off go if OBJECT_ID('dbo.salesOutputDataYear') is not null begin set noexec on end go create table dbo.salesOutputDataYear ( [salesPersonID] bigint not null , [salesYear] int not null , [salesAmount] money not null , [numberofItems] int not null , constraint PK_SalesOutputDataYear primary key ( [salesPersonID] , [salesYear] ) ) go set noexec off go

 

DDL – Create Stored Procedure

Here we create our simple Stored Procedure – dbo.usp_salesOuputData_Add.

  • It does a straight through insert into dbo.salesOutputData
  • It aggregates data into dbo.salesOutputDataYear – If record already exists for that SalePerson for that year, it updates the existing data.  If no record, then create new record
  • It supports conditional usage of SQL Server Merge Operation

 



use [DBLab] go if OBJECT_ID('dbo.usp_salesOutputData_Add') is null begin exec('create procedure dbo.usp_salesOutputData_Add as select 1/0 as [null] ') end go alter proc dbo.usp_salesOutputData_Add ( @operationMerge bit , @salesPersonID bigint , @salesDate datetime , @salesAmount money , @debug bit = 0 , @salesAmountTotal money = null output , @numberofItems int = null output ) as begin declare @iNumberofRecordsAffected int declare @salesYear int declare @tblOutput TABLE ( [salesAmount] money , [numberofItems] int ) set @iNumberofRecordsAffected = -1 set @salesYear = DATEPART(year, @salesDate) set @salesAmountTotal = 0 set @numberofItems = 0 insert into dbo.salesOutputData ( [salesPersonID] , [salesDate] , [salesAmount] ) values ( @salesPersonID , @salesDate , @salesAmount ) --merge yes or no if (@operationMerge = 0) begin if (@debug = 1) begin print 'dbo.salesOutputDataYear -- Update' end update tblSales set @salesAmountTotal = salesAmount = isNull(salesAmount, 0) + isNull(@salesAmount, 0) , @numberofItems = numberofItems = isNull(numberofItems, 0) + 1 from dbo.salesOutputDataYear tblSales where tblSales.salesPersonID = @salesPersonID and tblSales.[salesYear] = @salesYear set @iNumberofRecordsAffected = @@ROWCOUNT --if existing aggregate does not exist then, please insert if (@iNumberofRecordsAffected = 0) begin if (@debug = 1) begin print 'dbo.salesOutputDataYear -- Insert' end insert into dbo.salesOutputDataYear ( salesPersonID , [salesYear] , [salesAmount] , [numberofItems] ) values ( @salesPersonID , @salesYear , isNull(@salesAmount, 0) , 1 ) set @salesAmountTotal = @salesAmount end -- if iNumberofRecordsAffected = 0 end --if @merge = 0 else begin merge into dbo.[salesOutputDataYear] as target using ( select @salesPersonID , @salesYear , @salesAmount ) as ( salesPersonID , salesYear , salesAmount ) on ( (target.[salesPersonID] = @salesPersonID) and (target.[salesYear] = @salesYear) ) when matched then update set [salesAmount] = [target].[salesAmount] + @salesAmount , [numberofItems] = [numberofItems] + 1 when not matched then insert ( salesPersonID , [salesYear] , [salesAmount] , [numberofItems] ) values ( @salesPersonID , @salesYear , isNull(@salesAmount, 0) , 1 ) output inserted.[salesAmount] , inserted.[numberofItems] into @tblOutput ( [salesAmount] , [numberofItems] ) ; select top 1 @salesAmountTotal = [salesAmount] , @numberofItems = [numberofItems] from @tblOutput tblOutput end; --merge end; --end proc

 

DDL – Stored Procedure Invocation

 

Merge – Yes

Here is what a merge looks like …



set nocount on
set statistics io off
set statistics time off
go

use [DBLab]
go

truncate table dbo.salesOutputData
truncate table dbo.salesOutputDataYear

set statistics io on
go

declare @today datetime
declare @tomorrow datetime
declare @salesAmountTotal money
declare @debug bit

set @debug = 0
set @today = GETDATE()
set @tomorrow = @today + 1


begin tran

     print ''
     print 'Merge for Person ID - 201'
     print ''

     exec dbo.usp_salesOutputData_Add
             @salesPersonID = 201
           , @salesDate = @today
           , @salesAmount = 10000
           , @operationMerge = 1
           , @debug = @debug
           , @salesAmountTotal = @salesAmountTotal output

    print ''
    print 'Merge for same Person ID - 201'
    print ''
 
    exec dbo.usp_salesOutputData_Add
          @salesPersonID = 201
        , @salesDate = @tomorrow
        , @salesAmount = 20000
        , @operationMerge = 1
        , @debug = @debug
        , @salesAmountTotal = @salesAmountTotal output


rollback tran
go

Statistics I/O

 

Merge - Yes

 

Explanation:

  • Pasted above are two sets of SP Invocation
  • On distinctive aspect is the cost of accessing our table variable.  The first occurrence is populating it during the insert.  And, the second occurrence is reading from it to populate our local variable.

 

Merge – No



set nocount on
set statistics io off
set statistics time off
go

use [DBLab]
go

truncate table dbo.salesOutputData
truncate table dbo.salesOutputDataYear

set statistics io on
go

declare @today datetime
declare @tomorrow datetime
declare @salesAmountTotal money
declare @debug bit

set @debug = 0
set @today = GETDATE()
set @tomorrow = @today + 1


begin tran

     print ''
     print 'First Add for same Person ID (Update/Insert)'
     print ''

     exec dbo.usp_salesOutputData_Add
             @salesPersonID = 101
           , @salesDate = @today
           , @salesAmount = 10000
           , @operationMerge = 0
           , @debug = @debug
           , @salesAmountTotal = @salesAmountTotal output

    print ''
    print 'Second Add for same Person ID (Update):'
    print ''
 
    exec dbo.usp_salesOutputData_Add
          @salesPersonID = 101
        , @salesDate = @tomorrow
        , @salesAmount = 20000
        , @operationMerge = 0
        , @debug = @debug
        , @salesAmountTotal = @salesAmountTotal output


rollback tran
go

Statistics I/O

 

Merge-No

Explanation:

  • Once again pasted above are two sets of SP Invocation
  • In the first set we do not have existing data for our SQL and so during aggregation we attempted an insert into dbo.salesOuputDataYear but missed, and so in the very next step we processed row creation
  • In the second set we attempted an update, and has we now have existing data, we were fruitful and no need for creation of new row

 

Summary

It appears that in cases where we are dealing with singleton updates and we need to read the result of our aggregation, Transact SQL Server’s exposure of the results of our updates will alleviate the need of the output Clause and its associated cost.

Unfortunately, this expression is only available within an Update Clause.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s