Tag 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.

Quick list of DBCC (Database Console Commands) in SQL Server

Quick list of DBCC (Database Console Commands) in SQL Server

Database consistency check is a part of one of the important duty of DBA. I used to restore my each and every full database backup in one of the development server and execute DBCC command in that newly restored database. Once DBCC stops execution, I suppose to get an email with the status of this DBCC execution.

I have done automated process for all these steps, I will give more details about how to practically implement this process sometime later but today I wanted to show the list of some DBCC commands which are my favorite to check consistency of database.

Some of the DBCC commands given in following list may affect performance of SQL Server until it gets completed. If your database is large then some of the commands may take few hours for complete execution so be caution before running those commands in live environment, especially in business hours. As I have explained above, I used to restore my database backup somewhere and used to run DBCC command, not directly on production database, which is recommended method.

Anyway, let us have a look at different DBCC commands for different purpose.

Note: don’t run all commands together. Each DBCC command had different purpose and it is recommended to run each one of them separately and see behavior.

USE AdventureWorks2012
GO

--CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity
--If running CHECKDB, no need to run CHECKALLOC separately
DBCC CHECKDB;
GO

-- Check the AdventureWorks2012 database without
--nonclustered indexes and extended logical checks.
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH EXTENDED_LOGICAL_CHECKS;
GO

--Extended Logical Checks and Physical Only cannot be used together
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH PHYSICAL_ONLY;
GO

--Checks the consistency of disk space allocation structures for a specified database.
--In our case, it is AdventureWorks2012
DBCC CHECKALLOC;
GO

--Just an estimation and no elaborated messages
DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS

--Following command check only PRIMARY filegroup
DBCC CHECKFILEGROUP;
GO

--check specific filegroup and integrity of the physicalstructure of the page
DBCC CHECKFILEGROUP (1, NOINDEX) WITH PHYSICAL_ONLY;
GO

--Check data integirty for tables with different options for same table.
--execute each of the following three command separately and see the difference
DBCC CHECKTABLE ('sales.SalesOrderDetail') WITH ALL_ERRORMSGS
GO

DBCC CHECKTABLE ('sales.SalesOrderDetail') WITH ESTIMATEONLY
GO

DBCC CHECKTABLE ('sales.SalesOrderDetail', 1) WITH PHYSICAL_ONLY
GO

--Check for constraints violation on specified table or indexed view
DBCC CHECKCONSTRAINTS ('sales.SalesOrderDetail')
GO

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.

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms in SQL Server to give short name to your fully qualified object name

Synonyms is not a new enhancement in SQL Server 2012, it is long back introduced in SQL Server 2005 but I have seen many SQL Developer doesn’t know this feature so thought to give some light to this concept.

As per MSDN, A synonym is an alternative name for a schema-scoped object. In SMO, synonyms are represented by the Synonym object. The Synonym object is a child of the Database object. This means that synonyms are valid only within the scope of the database in which they are defined. However, the synonym can refer to objects on another database, or on a remote instance of SQL Server.

The object that is given an alternative name is known as the base object. The name property of the Synonym object is the alternative name given to the base object.

Synonym can be created on following:

Assembly (CLR) Stored Procedure

Assembly (CLR) Table-valued Function

Assembly (CLR) Scalar Function

Assembly Aggregate (CLR) Aggregate Functions

Replication-filter-procedure

Extended Stored Procedure

SQL Scalar Function

SQL Table-valued Function

SQL Inline-table-valued Function

SQL Stored Procedure

View

User Defined Table (Including local and global temporary tables)

You can alter data of object via synonyms but you can’t alter schema of object via synonym. For example I have table named “AdventureWorks2012.Sales.SalesPersonQuotaHistory” in Adventureworks2012 database and I give synonyms “SalesPersonQuota” then I can execute any DML commands (INSERT / UPDATE / DELETE) on “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table but I can’t Add/Remove column or change datatype of column in “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table.

Nowadays, we used to create proper schema for each object and detail oriented name to object which makes object name longer so it is good to give short but meaningful name to object to increase productivity (though we have intelisense now).

By providing synonyms name to developer for use in their script, you are also ensuring that they will not be able to change definition of the object via synonyms (though we can handle this with proper login/user permission). This is one more advantage as per me. Each business process and environment has their own rules and regulation to apply. We may have debate on whether this is useful or not as I am sure few people would appreciate the usage of synonyms whereas few will stand against it. Personally I would like to use synonyms for the benefit I just explained.

Let us see how we can create and use synonyms. I will use “Adventureworks2012” database for this demonstration and table would be “AdventureWorks2012.Sales.SalesPersonQuotaHistory”.

USE AdventureWorks2012
GO

CREATE SYNONYM SalesPersonQuota
FOR AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

Now if we query original table or query synonyms, we will get same results, let us confirm it.

SELECT TOP 10 * FROM AdventureWorks2012.Sales.SalesPersonQuotaHistory
GO

SELECT TOP 10 * FROM SalesPersonQuota
GO

I have received the same output. Have a look:

SynonymsOutput

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.