MERGE WHEN MATCHED

MERGE statement can be use to modify data in a target table based on data in a source table. Merge joins the target to the source by using a column common in both tables, such as parent-child key column. You can then insert, modify, or delete data from the target table, all in one statement, according to how the rows match up as a result of the join.

The MERGE statement comes up with many clauses that facilitate the different types of data modifications.

In the first part of article we will look the When MATCHED clause later in next part we will see When NOT MATCHED [Source]/[Target] .

To facilitate our article, let’s take an example and demonstrate how it work, we are here creating two tables MarblesCounts_KancheKhelega as target table and MarblePurchased_ChalKancheLene as source table

create table dbo.MarblesCounts_KancheKhelega  -- target

(

  marble_id int not null primary key,

  marble_color varchar(40) not null ,

  marble_count int not null

    constraint MarbleCount_Default_1 default 0

);

Command(s) completed successfully.

create table dbo.MarblePurchased_ChalKancheLene  -- source

(

  marble_id int not null primary key,

  marble_color varchar(40) not null,

  marble_count int not null

    constraint MarbleCount_Default_2 default 0

);

Tables are in place let’s inserts few records in it.

insert into MarblesCounts_KancheKhelega values

  (1, 'White wale bade Kanche', 6),

  (2, 'Pale Yellow wale Dimond Kanche', 3),

  (3, 'Red Ramesh ke kanche', 0),

  (5, 'Green Suresh ke churaye kanche', 0),

  (6, 'Purple Kamal se jite kanche', 0),

  (8, 'Blue har game jitane wala lucky kanche', 4);

(6 row(s) affected)

insert MarblePurchased_ChalKancheLene values

  (1, 'White wale bade Kanche', 3),

  (3, 'Red Ramesh ke kanche', 0),

  (4, 'Grey bade unlucky kanche', 4),

  (5, 'Green Suresh ke churaye kanche', 5),

  (7, 'Silver bade expensive Kanche', 8);

(5 row(s) affected)

Querying the table to see the inserted records.

select * from  MarblesCounts_KancheKhelega

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   6

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

5           Green Suresh ke churaye kanche           0

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

select * from MarblePurchased_ChalKancheLene

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           5

7           Silver bade expensive Kanche             8

Since we have table with data pupulated, now it is the time to jump into MERGE and see how it works

WHEN MATCHED Clause

The first MERGE clause we’ll look at is WHEN MATCHED. You should use this clause when you want to update or delete rows in the target table that match rows in the source table. Rows are considered matching when the joined column values are the same.

For example, if the marble_id value in the MarblesCounts_KancheKhelega table matches the marble_id  value in the MarblePurchased_ChalKancheLene table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the MarblesCounts_KancheKhelega table (the target) to the MarblePurchased_ChalKancheLene table (the source) and then use a WHEN MATCHED clause to update the marble_count column in the target table:

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count;

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   9

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

5           Green Suresh ke churaye kanche           5

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

(6 row(s) affected)

Suppose you want to remove from the MarblesCounts_KancheKhelega table any marble whose Quantity value is 0 in both the target and source tables. You can easily delete such rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown in the following example

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED AND

  kc.marble_count + ckk.marble_count = 0 then

  delete

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count;

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   12

2           Pale Yellow wale Dimond Kanche           3

5           Green Suresh ke churaye kanche           10

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

(5 row(s) affected)

So here we can see those records whose marble_id matched in both the table and whose sum of marble count in MarblesCounts_KancheKhelega and MarblePurchased_ChalKancheLene are removed.

Programming is the fuel of Saturday and Sunday 

Post Reference: Vikram Aristocratic Elfin Share

Consequence of Redundant Index on your table.

If have multiple queries where your searching criteria varies from col1 in first query, col1 and col2 in second query, col1 and col3 in third query, how you will be proceeding with index creation.

Let’s stimulate the whole situation by taking an example; we are creating a table employee with some columns.

set nocount on

go

create table dbo.Employee

(emp_id int identity primary key,

first_name varchar(10),

last_name varchar(10),

address varchar(20),

city varchar(10),

state varchar(10),

active bit)

Command(s) completed successfully.

Our table is ready, now lets insert some data into it.

declare @cnt int, @id varchar(4)

select @cnt = 1

while (@cnt <= 10000)

begin

      select @id = cast(@cnt as varchar)

      insert into Employee(first_name, last_name, address, city, active)

      select 'f'+@id, 'l'+@id, 'a'+@id, 'c'+@id, 1

      select @cnt = @cnt + 1

end

Command(s) completed successfully.

Now since we have table Employee in position lets create indexes on the basis of predicate used in the select queries.

create index ix_emp_lastname on dbo.Employee(last_name)

create index ix_emp_lastname_firstname on dbo.Employee(last_name, first_name)

create index ix_emp_lastname_active on dbo.Employee(last_name, active)

go

Now here we have created three indexes on Employee, first on last_name column, second on last_name and first_name, third on last_name and active column.

Lets check out the space used by the index by executing sp_spaceused

exec sp_spaceused Employee

name      rows  reserved  data    index_size unused

--------------- --------- ------- ---------- -------

Employee  10000 1376 KB   432 KB  712 KB     232 KB

So the index is using 712Kb of space. Now lets fire each query and check out the execution plan to see how and which index it is using,

select emp_id from dbo.Employee

where last_name = 'l34'

The first query used index ix_emp_lastname_active, now lets run the second query and see the execution plan

select emp_id from dbo.Employee

where last_name = 'l34' and active = 1

The second query used index ix_emp_lastname_active, now lets run the third query and see the execution plan

select emp_id from dbo.Employee

where first_name = 'f34' and last_name = 'l34'

The third query used index ix_emp_lastname_firstname, now lets run the fourth query and see the execution plan

select emp_id from dbo.Employee

where first_name = 'f34' and last_name = 'l34' and active = 1

Now these are interesting. The first and second queries used index ix_emp_lastname_active. The third query used index ix_emp_lastname_firstname. For the last query, the optimizer decided to use ix_emp_lastname_firstname as well and decided to do a lookup to get the actives value.

NOTE (Intresting Look) : If you haven't noticed yet, index ix_emp_lastname was not used to satisfy any of these queries. It's just sitting around, literally taking up space.

Now let's drop these indexes and add a single index to satisfy the same four queries

drop index dbo.Employee.ix_emp_lastname

drop index dbo.Employee.ix_emp_lastname_firstname

drop index dbo.Employee.ix_emp_lastname_activesw

Lets create a single index which will satisfy the all the query

create index ix_emp_lastname on dbo.Employee(lastname, firstname, active)

If we now re-run sp_spaceused, we'll see that the index space used by the table has been reduced by over 100%

exec sp_spaceused Employee

name     rows        reserved           data    index_size  unused

-------------------- ------------------ ------  ----------  ---------

Employee 10000       846 KB             432 KB  304 KB       112 KB

Lets again execute those queries and see the new execution plan.

select emp_id from dbo.Employee where last_name = 'l34'

Now the first query used index ix_emp_lastname, now lets run the second query and see the execution plan

select emp_id from dbo.Employee where last_name = 'l34' and active = 1

The second query is also using the same index ix_emp_lastname, now lets run the third query and see the execution plan

select emp_id from dbo.Employee where first_name = 'f34' and last_name = 'l34'

Again we can see the third query is also using the same index ix_emp_lastname, now lets run the fourth query and see the execution plan

select emp_id from dbo.Employee where first_name = 'f34' and last_name = 'l34' and active = 1

The fourth query is also using the same index.

Conclusion: the single index satisfies all four queries. In addition, the last query that performed a lookup for the actives now reads it directly from the index and does not resort to searching the clustered table for the value.

If you desire to kill, kill me through code;honor me HEAVEN.

Post Reference: Vikram Aristocratic Elfin Share