MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008which is really underutilized, I have seen many time recently that developersare still using separate DML statement for Insert / Update and Delete wherethere is a chance they can use MERGE statement of they can use condition based Insert/ Update and Delete in one shot. 
This will give performance advantage as complete process isgoing to read data and process it in one shot rather than performing singlestatement to table each time you write.
I will give you one small example so that you can see howone can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where wecan find Memberid, member name, registration date and expiration date. There isone more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin tablewhose expiration date has been met, we want to set default password for thosemember who are not expired right now and we want to make entry of those userwho are just registered and id/password is not set yet.
–createMember’s personal detail table and insert data in it.
Create Table MemberPersonalDetail
(
MemberID INT Identity(1,1),
MemberName Varchar(20),
RegisterDate date,
ExpirationDate date
)
GO
INSERT INTO MemberPersonalDetail
SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL
SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL
SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′
GO
SELECT * FROMMemberPersonalDetail
go
–createMember’s login detail table and insert data in it.
CREATE TABLE MemberLoginDetail
(
MemberID INT,
UserName varchar(20),
UserPassword varchar(20)
)
GO
INSERT INTO MemberLoginDetail
SELECT 1,‘Ritesh Shah’,‘TestPassword’ UNION ALL
SELECT 2,‘Rajan Shah’,‘goodluck’
GO
SELECT * FROMMemberLoginDetail
go
–MERGEstatement with Insert / Update / Delete…..
–if you justneed Insert / update or Insert / delete or Update / Delete anyting
— you can useany combo
— I haveexplained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDateFROM MemberPersonalDetail) AS mpd
ON mld.MemberID = mpd.MemberID
WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE
WHEN MATCHED THEN UPDATE SET mld.UserPassword = ‘DefaultPassword’
WHEN NOT MATCHED THEN
INSERT(MemberID,UserName,UserPassword)
VALUES(mpd.memberID,mpd.MemberName,‘DefaultPassword’);
GO
–check thetable whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008which is really underutilized, I have seen many time recently that developersare still using separate DML statement for Insert / Update and Delete wherethere is a chance they can use MERGE statement of they can use condition based Insert/ Update and Delete in one shot. 
This will give performance advantage as complete process isgoing to read data and process it in one shot rather than performing singlestatement to table each time you write.
I will give you one small example so that you can see howone can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where wecan find Memberid, member name, registration date and expiration date. There isone more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin tablewhose expiration date has been met, we want to set default password for thosemember who are not expired right now and we want to make entry of those userwho are just registered and id/password is not set yet.
–createMember’s personal detail table and insert data in it.
Create Table MemberPersonalDetail
(
MemberID INT Identity(1,1),
MemberName Varchar(20),
RegisterDate date,
ExpirationDate date
)
GO
INSERT INTO MemberPersonalDetail
SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL
SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL
SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′
GO
SELECT * FROMMemberPersonalDetail
go
–createMember’s login detail table and insert data in it.
CREATE TABLE MemberLoginDetail
(
MemberID INT,
UserName varchar(20),
UserPassword varchar(20)
)
GO
INSERT INTO MemberLoginDetail
SELECT 1,‘Ritesh Shah’,‘TestPassword’ UNION ALL
SELECT 2,‘Rajan Shah’,‘goodluck’
GO
SELECT * FROMMemberLoginDetail
go
–MERGEstatement with Insert / Update / Delete…..
–if you justneed Insert / update or Insert / delete or Update / Delete anyting
— you can useany combo
— I haveexplained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDateFROM MemberPersonalDetail) AS mpd
ON mld.MemberID = mpd.MemberID
WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE
WHEN MATCHED THEN UPDATE SET mld.UserPassword = ‘DefaultPassword’
WHEN NOT MATCHED THEN
INSERT(MemberID,UserName,UserPassword)
VALUES(mpd.memberID,mpd.MemberName,‘DefaultPassword’);
GO
–check thetable whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Filtered Index in SQL Server 2008/Denali

Filtered Index is nothing but just a feature of Nonclustered index which I shown in previous two articles. It is just a nonclustered index with WHERE clause in simple terms.
It is mainly used while you have big tables and you used toselect only subset of data from that table. Like you have one big customertable and have one field of “Reference Person” in that table, it has NULL valueif customer directly comes to us and has reference person’s name, if customercame from any of the reference. In this case if you want only those customerslist that has reference person so that we can distribute some sort ofconsolation to those reference people.
The main advantage of “Filtered Index” is, it will havelower amount of rootpages to store the data as it will consider only those rows which cater theneeds of “Where” clause of “Filtered Index”.
Less number of pages means reduced storage size.  Since “Filtered Index” has only those data inroot pages which caters the need of “Where” clause, means when you perform anyDML operation like Insert, Delete or Update, “Filtered Index” will get effectonly if it affects the Index Key which comes under the “Where” clause of Indexso low maintenance cost. 
BTW, you can’t create “Filtered Index” on View but it willsurely get benefit of the “Filtered Index” created on base table.
Let us check the impact of  “Filtered Index” practically.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable is already there. you can delete it than create new one with name”Orders”
IF OBJECT_ID(‘SQLHubFilteredIndex1’,‘U’) IS NOT NULL BEGIN
      DROP TABLE SQLHubFilteredIndex1
END
GO
–creating table
CREATE TABLE SQLHubFilteredIndex1 (ID INT IDENTITY Primary Key Clustered, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting fackrows into table
INSERT INTO SQLHubFilteredIndex1 (OrderDate, Amount, Refno)
SELECT TOP 100
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
Union All
SELECT TOP 100000
      NULL,
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–run thefollowing query with execution plan together and see the results in executionplan
–you can seeexecution plan with the following steps
–first selectboth of the below given query
–Press Ctrl+M
–press F5
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
CREATE NONCLUSTERED INDEXidx_SQLHubFilteredIndex1 ONSQLHubFilteredIndex1(OrderDate)
WHERE OrderDate is not null
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
GO
–if you wish,you can uncomment below code and delete SQLHub database
—-use master
—-go
—-dropdatabase sqlhub
You can see in above screen shot that the same query ranfaster after creating index.

if you want to refer all other articles related to index, click here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Filtered Index in SQL Server 2008/Denali

Filtered Index is nothing but just a feature of Nonclustered index which I shown in previous two articles. It is just a nonclustered index with WHERE clause in simple terms.
It is mainly used while you have big tables and you used toselect only subset of data from that table. Like you have one big customertable and have one field of “Reference Person” in that table, it has NULL valueif customer directly comes to us and has reference person’s name, if customercame from any of the reference. In this case if you want only those customerslist that has reference person so that we can distribute some sort ofconsolation to those reference people.
The main advantage of “Filtered Index” is, it will havelower amount of rootpages to store the data as it will consider only those rows which cater theneeds of “Where” clause of “Filtered Index”.
Less number of pages means reduced storage size.  Since “Filtered Index” has only those data inroot pages which caters the need of “Where” clause, means when you perform anyDML operation like Insert, Delete or Update, “Filtered Index” will get effectonly if it affects the Index Key which comes under the “Where” clause of Indexso low maintenance cost. 
BTW, you can’t create “Filtered Index” on View but it willsurely get benefit of the “Filtered Index” created on base table.
Let us check the impact of  “Filtered Index” practically.
–create onedatabase which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orderstable is already there. you can delete it than create new one with name”Orders”
IF OBJECT_ID(‘SQLHubFilteredIndex1’,‘U’) IS NOT NULL BEGIN
      DROP TABLE SQLHubFilteredIndex1
END
GO
–creating table
CREATE TABLE SQLHubFilteredIndex1 (ID INT IDENTITY Primary Key Clustered, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting fackrows into table
INSERT INTO SQLHubFilteredIndex1 (OrderDate, Amount, Refno)
SELECT TOP 100
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
Union All
SELECT TOP 100000
      NULL,
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–run thefollowing query with execution plan together and see the results in executionplan
–you can seeexecution plan with the following steps
–first selectboth of the below given query
–Press Ctrl+M
–press F5
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
CREATE NONCLUSTERED INDEXidx_SQLHubFilteredIndex1 ONSQLHubFilteredIndex1(OrderDate)
WHERE OrderDate is not null
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
GO
–if you wish,you can uncomment below code and delete SQLHub database
—-use master
—-go
—-dropdatabase sqlhub
You can see in above screen shot that the same query ranfaster after creating index.

if you want to refer all other articles related to index, click here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Update NULL records in all columns with any value which is NOT NULL in same column SQL Server 2005/2008

Today I gave solution for one strange problem in one of the forum; I thought to share that script with all of you. Requirement was something like below:

— Update all field of Table which is NULL

–NULL data should be populated with NOT NULL value of the same column

Well, this is somehow strange but it was needed so I quickly create one small script with the help of cursor, however, I always avoid cursor as long as possible. I didn’t find any other quick solution at that time.

–create table for demo

if OBJECT_ID(’emps’,‘U’) is not null drop table emps

CREATE TABLE [dbo].[emps](

      [Name] [varchar](50) NULL,

      [Dept] [varchar](10) NULL,

      [Company] [varchar](15) NULL

) ON [PRIMARY]

 

GO

–insert some data

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘CHEM’ UNION ALL

SELECT ‘RAJAN’,NULL,NULL UNION ALL

SELECT NULL,‘ACCT’,‘MAR’

GO

 

–script with cursor

declare @SQL nvarchar(max)

DECLARE @ColName VARCHaR(15)

set @SQL=

 

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=’emps’

 

OPEN FirstCur

FETCH FROM FirstCur INTO @ColName

 

WHILE @@FETCH_STATUS=0

BEGIN

      SET @SQL=@SQL+ ‘ Update Emps SET ‘ + @ColName + ‘ = (SELECT top 1 ‘ + @ColName + ‘ FROM emps where ‘ + @ColName + ‘ is not null) where ‘ + @ColName + ‘ is null; ‘

      FETCH NEXT FROM FirstCur INTO @ColName

END

print @sql

CLOSE FirstCur

DEALLOCATE FirstCur

exec sp_executeSQL @SQL

go

 

–CHECK DATA

select * from emps

Happy Coding!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com