SQL Server Performance Series – Union vs Union All

By Navratan Verma

Union Operator  is used to combine multiple result sets into one and also removes any duplicates rows that exist.  it gives distinct values across all columns in the result set.

UNION ALL OPERATOR:  is used to combine multiple result sets into one, but it does not remove any duplicate rows.  Since this does not remove duplicate rows the cost is less and performs faster, but if you don’t want duplicate records you will need to use the UNION operator instead.

Performance TIP:  Compared UNION ALL operator, UNION operator has the extra overhead of removing duplicate rows and sorting results. So, If we know that all the records returned by our query is unique from union then use UNION ALL operator instead of UNION Operator.

Download the script used here : Union

Here is the execution plan results First query is uses union and second union all.

UnionAll

Thanks

SQL Server Performance Series – Unfiltered Delete

By Navratan Verma

It was interesting thing observed at one of the client where the developer was using unfiltered delete on a staging table which was further used down in the stored procedure.

Delete from TempLoanPayment

When there is a need to delete records from staging table without any condition it is better to use truncate rather than using delete, as delete requires to update any underlying index and also a fully logged operation.

DeleteNonFil

It is recommended to use Truncate table TempLoanPayment

Note: Truncate table should not used on table having referred into foreign key constraint and truncate resets the identity column.

Thanks

 

Cardinality Estimation in SQL 2014

By Navratan Verma

Here is the quick exercise performed today to evaluate Cardinality Estimation in SQL 2014.

Installed SQL Server 2014 and restored the database AdventureWorks2008.

Executed the following Script get the Full Script Here.  SQL2014_Cardinality

Cardinality1

Can the below query with Actual Execution Plan.

CardQUery1

Execution Plan Results – No of Rows Returned – 298024


 

Cardinality2


 

Set the Compatibility mode to 120 (SQL 2014) and re-run the query.

CompMode2

Execution Plan Results – No of Rows Returned – 298024

CardinalityFn

SQL 2014 – seems to have made good improvement in this critical area.

SQL Server – Implicit Scans

By Navratan Verma

I was working with one of the client and saw an stored procedure doing table scan where it was fetching FirstName, LastName and MobileNumber based on FirstName of the person.

The table structure is as follows.

Column_name    Type
EmpID                       int
FirstName                 varchar
LastName                  varchar
MobileNumber         varchar
EMail                          varchar

Note: This table has a index on FirstName column covering (LastName,MobileNumber)

Here is an example of data retrieval using Simple Select.

Implicit1

The above query seems perfect retrieving the desired results with Index seek.

Here is the catch, there was a stored procedure to retrieve data from the above table.

CREATE PROCEDURE GetEmpDetails
@FirstName NVARCHAR(50)
AS
select FirstName,LastName,MobileNumber  From SourceTable where FirstName = @FirstName
GO

implicit2

Root cause the stored procedure uses NVARCHAR in parameter where as the firstname column has a datatype
varchar(500) changing it to Varchar(50) will make it use the index and go for index seek.

ALTER PROCEDURE GetEmpDetails
@FirstName VARCHAR(50)
AS
select FirstName,LastName,MobileNumber  From SourceTable where FirstName = @FirstName
GO

In a large environment where these stored procedure was called several times in a day would result in bad performance, this is just a simple tip to avoid table scan which can be easily avoided.

Thanks.

SQL – MERGE Statement

By Navratan Verma

Usage of T-SQL Merge Statement to Insert / Update data from Source table to Destination.

Download the Script used in Demo : MergeDemo

Setup Database.

merge1

  Create Source Table and populate data

merge2

Display data from source table, create Destination Table.

merge3

Populate data into Destination table using Merge Statement.

merge4

See the Results in Destination table

Select * From Destination

merge5

Insert and Modify Record in Source Table

Insert into SourceTable (EmpID,FirstName,LastName,MobileNumber,EMail) values (6,’Sachin’,’Tendulkar’,’9034287428′,’sachin.tendulkar@gmail.com’)

Update SourceTable set MobileNumber=9739051344, EMail=’kanishk.verma@gmail.com’ where EmpID=5

 Run the Merge Statement Again
MERGE  Destination AS Target
USING SourceTable AS Source
ON Target.EmpID = Source.EmpID
WHEN MATCHED
AND (Target.FirstName <> Source.FirstName
OR Target.LastName <> Source.LastName
OR Target.MobileNumber <> Source.MobileNumber
OR Target.Email<> Source.Email
)
THEN
UPDATE SET
Target.FirstName = Source.FirstName
,Target.LastName = Source.LastName
,Target.MobileNumber = Source.MobileNumber
,Target.Email = Source.Email
WHEN NOT MATCHED BY TARGET
THEN
INSERT (EmpID,FirstName,LastName,MobileNumber,EMail)
VALUES (Source.EmpID, Source.FirstName, Source.LastName, Source.MobileNumber,Source.Email);

 Verify Data Insert and Update

merge6

Thank you.