Merge Interval Operator – Part1

Hi Geeks,

Merge interval operator used to remove duplicated predicates in a query. It also helps to find possible overlapping intervals which in turn helps to optimize filters to avoid scanning same data more than once.

Let us start with following query then jump into the subject.

USE [AdventureWorks2012]

SELECT SUM(Product.SafetyStockLevel)
FROM [Production].[Product]
WHERE Product.ProductID IN (1,2,3,4)

From the above plan, clustered index seek performed on ProductID to fetch data specified in IN clause then query optimiser used StreamAggregate to compute sum. As of now we do not see Merge Interval in the query plan. Let me modify our query little bit as following.

USE [AdventureWorks2012]

DECLARE @ID1 Int = 1,
		@ID2 Int = 2,
		@ID3 Int = 3,
		@ID4 Int = 4 

SELECT SUM(Product.SafetyStockLevel)
FROM [Production].[Product]
WHERE Product.ProductID IN (@ID1,@ID2,@ID3,@ID4)

Note: Query plan image is broken into two parts in order to fit into the window.

I’ve used variables instead of constant values and for that query optimizer created a different plan altogether. The reason behind this is, SQL Server does not know the values of the constants and in case these values turns out to be duplicates it will read same data twice and for the same reason it uses Merge intervals to remove duplicate occurrences.

We are going to explore more on this tomorrow, stay tuned.

Happy learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook