Category Archives: TSQL

Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server

I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc.

Let us see this practically with an example. Create one sample table with row given in following TSQL Script.

CREATE TABLE Employee 
(ID INT IDENTITY(1,1)
, Name VARCHAR(10)
, Dept VARCHAR(10) ) 
GO 

INSERT INTO Employee 
SELECT 'Ritesh','DBA' 
UNION ALL 
SELECT 'Rajan','Dev' 
UNION ALL 
SELECT 'Bihag','Net' 
GO 

Now we have situation that if we pass “ALL” from application, it should show data from all department otherwise it should show data from the specific department which is provided in the variable. Let us see method one where people used to use IF condition to check value and have two separate SELECT statement. I don’t prefer this one, why we should write two SELECT statement whereas we can write only single. If we have many conditions, we have to repeat SELECT query in this method which is not preferable from the maintenance point of view.

--Method 1: 
DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 
IF @Dept='ALL' 
BEGIN 
	SELECT * FROM Employee 
END ELSE 
BEGIN 
	SELECT * FROM Employee WHERE Dept=@Dept 
END

There are some people who uses method 2 where they take value in temporary table to find filter value and pass it to main SELECT clause so in this method you will have only one main SELECT clause but this is also less preferable method as it has multiple SELECT statement in set along with temporary table which affect performance adversary in most cases so don’t use temporary table unless there is no way around.

--Method 2:

DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 
IF OBJECT_ID('tempdb..#DeptList') IS NOT NULL 
	DROP TABLE #DeptList 
CREATE TABLE #DeptList (
Dept VARCHAR(10)
)  

IF @Dept='ALL' 
BEGIN 
	INSERT INTO #DeptList (Dept) 
	SELECT Dept FROM Employee 
END ELSE 
BEGIN 
	INSERT INTO #DeptList (Dept) 
	SELECT Dept FROM Employee 
	WHERE Dept=@Dept 
END 

SELECT * FROM Employee WHERE Dept IN (SELECT Dept From #DeptList) 
GO 

/*method 3 which is my preferable shortest method in this kind of situation which has good performance as well when compare it with above two method.*/   

--Method 3 
DECLARE @Dept VARCHAR(10) 
SET @Dept = 'ALL' 

SELECT * FROM Employee WHERE Dept=CASE WHEN @Dept='ALL' THEN Dept ELSE @Dept END

 

Are you using any other method to achieve the task explained here? It is interesting to discuss…!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah http://Extreme-Advice.com http://www.sqlhub.com Note: Microsoft Books online is a default reference of all articles.

Execute INSERT, UPDATE, DELETE statement together with MERGE statement in SQL Server

MergeExecute INSERT, UPDATE, DELETE statement together with MERGE statement in SQL Server

I used to review new stored procedure which is going to be live. Recently I have seen that one of the developers has developed one stored procedure which had logic of inserting records if it doesn’t exists already and update some value if record is already exists. He has used complex logic with IF…ELSE and SELECT…EXISTS. Since we are having SQL Server 2008 R2 in that particular server, it is not need to develop complex logic but we can simply use MERGE statement which was introduced from SQL Server 2008.

MERGE statement was one of the very good enhancements in TSQL in SQL Server 2008 though people don’t use to use it much due to unawareness.

MERGE statement will allow you to use any combination of INSERT, UPDATE & DELETE with simple MERGE statement and without having any complex logic which used to have till SQL Server 2005. Apart from that MERGE statement read and process data at once so that you reduce roundtrip of the server which ultimately enhances the performance.


USE AdventureWorks2012
 GO
 MERGE sales.SalesOrderDetail AS SOD
 USING
 (SELECT SalesOrderID,CustomerID,DueDate FROM Sales.SalesOrderHeader) AS SOH
 ON SOD.SalesOrderID=SOH.SalesOrderID
 WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=776 THEN DELETE
 WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=777 THEN UPDATE SET SOD.OrderQty=11
 WHEN NOT MATCHED THEN INSERT(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID)
 VALUES(SOH.SalesOrderID,'4911-403C-98',11,776);
 GO
 

Above query will delete one record from SalesOrderDetail table and Update one record in SalesOrderDetail table in AdventureWorks database. It won’t insert any record as there is no record which is exists in SaleOrderHeader table which doesn’t have child entry in SalesOrderDetails table.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

DISTINCT keyword and query performance in SQL Server

0UniqueDISTINCT keyword and query performance in SQL Server

DISTINCT is really useful and handy whenever you wanted to have distinct value in your result set but nothing comes free and DISTINCT keyword is not an exception. If your database is properly normalized, you face less issue to use DISTINCT. DISTINCT and performance of SELECT query is long debated topic and today I come up with my findings and experience with DISTINCT.

I have also seen some myth hovering around DISTINCT. I have seen few very popular blog post which suggest to use GROUP BY or sub query or EXISTS over the DISTINCT keyword to gain performance over DISTINCT keyword but personally I don’t believe it. SQL Server is very smart and it always try to find best suited execution plan for your query. BTW, I will not give name of those blog post and author, however if you are eager then you can search google with terms something like “speed up DISTINCT query” or “performance of DISTINCT in SQL Server” or anything else which has “DISTINCT” and “Performance” together.

Anyway, let us move on and find the effect of DISTINCT keyword on SELECT query. I have prepared demonstration in AdventureWorks2012 database so either download it from CODEPLEX website or use your own database by changing table name in the queries.

Here are two simple SELECT query with and without DISTINCT. You can see that query with DISTINCT keyword uses more resource.

--It is HIGHLY recommended that, DON'T use following two DBCC
--commnad on production environment. this is just for testing
--purpose in developement/test environent
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT DISTINCT SalesOrderID FROM Sales.SalesOrderDetail
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SalesOrderID FROM Sales.SalesOrderDetail
GO

Here is the screen capture of execution plan for both queries we have executed above:

1SimpleSelectwithDistinct

Query with DISTINCT keyword is taking 77% of total batch execution time whereas without SELECT returning more row though it takes only 23%. I have tried to get unique SalesOrderID from SalesOrderDetail table, I should have get it from SalesOrderHeader table as there would be less records with unique SalesOrderID. This query could be considered as bad designed query.

Now let us see whether there is any performance enhancement of GROUP BY or Sub Query or Exists or IN / NOT IN over DISTINCT key words.

Here are some different queries which returns same data. I have used DBCC command before each query so that previously created execution plan won’t be used.

--It is HIGHLY recommended that, DON'T use following two DBCC
--commnad on production environment. this is just for testing
--purpose in developement/test environent
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT DISTINCT SO.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH INNER JOIN
Sales.SalesOrderDetail AS SO
ON SH.SalesOrderID=so.SalesOrderID
WHERE SO.ProductID=776
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SH.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH
WHERE EXISTS
(SELECT 1 FROM Sales.SalesOrderDetail AS SO WHERE SO.SalesOrderID=SH.SalesOrderID AND SO.ProductID=776)
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SH.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH
WHERE SH.SalesOrderID IN
(SELECT SO.SalesOrderID FROM Sales.SalesOrderDetail AS SO WHERE SO.ProductID=776)
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SO.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH INNER JOIN
Sales.SalesOrderDetail AS SO
ON SH.SalesOrderID=so.SalesOrderID
WHERE SO.ProductID=776
GROUP BY SO.SalesOrderID, SH.DueDate,SH.TotalDue
GO

You can see each of the query above will return same number of row with exact same data, moreover there is no performance enhancement. No matter what condition or keyword you use, SQL Server will choose best suited plan for executing your query. You can confirm this by looking at following screen capture of execution plan.

2SELECTwithDISTINCTandOtherOption

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.