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

 

List SQL Server Agent JOBS in SQL Server 2008/2005/2000

As a DBA we may need to set the JOB for various task and those tasks will be saved under MSDB database. You can see it from EM or from SSMS GUI tools but it would help sometime to execute query to see the list of available JOB in SQL Server 2000+ versions.
There are two ways to achieve that task. Have a look at it.
–with stored procedure
EXEC MSDB..sp_HELP_JOB


–with sysjobs view
select * from msdb..sysjobs



Both of the above statements will give you list of JOB have been set in SQL Server Agent along with so many other important details.
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

Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

 

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

Use of CUBE and ROLLUP – Difference of CUBE and ROLLUP in SQL Server 2005/2008

SQL Server 2005+ came up with exciting facility of CUBE and ROLLUP clause. Herewith, I am going to show you use and difference of CUBE and ROLLUP in SQL Server 2005 and SQL Server 2008.

CUBE:  generates a result set that represents aggregates for all combinations of values in the selected columns

ROLLUP:  generates a result set that represents aggregates for a hierarchy of values in the selected columns

Let us see one practical scenario which will make your concept much clear about both these exciting features.

–Table 1 for Demo

create table dbo.Orders

(

OrderID varchar(5),

OrderDate varchar(50)

)

 

–date for table1

insert into dbo.Orders

select ‘A1000’,GETDATE()-1 union all

select ‘A1001’,GETDATE()

 

–table 2 for demo

create table dbo.OrderDetails

(

OrderID varchar(5),

SampleNo Varchar(8),

SampleDate varchar(50)

)

 

–data for table 2

insert into dbo.OrderDetails

select ‘A1000’,‘A1000-01’,GETDATE()-1 union all

select ‘A1000’,‘A1000-02’,GETDATE() union all

select ‘A1000’,‘A1000-03’,GETDATE() union all

select ‘A1001’,‘A1001-01’,GETDATE() union all

select ‘A1001’,‘A1001-02’,GETDATE()

 

–let us check both table

select * from dbo.Orders

select * from dbo.OrderDetails

 

–let us check how many samples came for each order id

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

 

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

 

(2 row(s) affected)

 

*************************************************************/

 

 

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with cube

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

 

select o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with rollup

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

–You will not be able to find big difference between ROLLUP and CUBE with above query.

–isn’t there any difference at all? NO, there is a difference. let us see the difference.

 

 

 

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with cube

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

——————– ————————————————– ————

A1000                May 24 2009  3:53PM                                1

Total Sample on Date May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1001                May 25 2009  3:53PM                                2

Total Sample on Date May 25 2009  3:53PM                                4

Total Sample on Date Total Sample for OrdID                             5

A1000                Total Sample for OrdID                             3

A1001                Total Sample for OrdID                             2

 

(8 row(s) affected)

 

*************************************************************/

 

 

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with rollup

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

——————– ————————————————– ————

A1000                May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1000                Total Sample for OrdID                             3

A1001                May 25 2009  3:53PM                                2

A1001                Total Sample for OrdID                             2

Total Sample on Date Total Sample for OrdID                             5

 

(6 row(s) affected)

 

*************************************************************/

In last two queries, you find the difference between CUBE and ROLLUP. Second from last query with CUBE shows you 8 rows while last query which is using ROLLUP, showing 6 rows. CUBE will show you sample received for each orderID on each day and finally it will show you total sample received whereas ROLLUP will show you bit less summary like: which day for which ORDERID, how many samples come in. and finally total sample received.

 

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