Unpivot implementation with NOT NULL COlumn

Rishabh K 2016-08-08 11:11:22

Hi ,

I want to understand why SQL Server Optimizer checks for NOT NULL Filter in case of unpivot even when I have defined columns as NOT NULL.

CREATE TABLE Unpvt_test(id int , FM01 FLOAT NOT NULL, FM02 FLOAT NOT NULL, FM03 FLOAT NOT NULL)
INSERT INTO Unpvt_test
VALUES
(1,10.00,11.00,12.00)
,(2,20.00,21.00,22.00)
,(3,30.00,31.00,32.00)

Here is the below details from SET STATISTICS PROFILE

SELECT * FROM   (  select id, FM01 ,  FM02,FM03   FROM Unpvt_test  )p  UNPIVOT   (   Rate for RateMonth IN (FM01,FM02,FM03)  )Unpvt
  |--Filter(WHERE:([Expr1007] IS NOT NULL))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Rishabh].[dbo].[Unpvt_test].[FM01], [Rishabh].[dbo].[Unpvt_test].[FM02], [Rishabh].[dbo].[Unpvt_test].[FM03]))
            |--Compute Scalar(DEFINE:([Expr1003]=[Rishabh].[dbo].[Unpvt_test].[id]))
            |    |--Table Scan(OBJECT:([Rishabh].[dbo].[Unpvt_test]))
            |--Constant Scan(VALUES:((N'FM01',[Rishabh].[dbo].[Unpvt_test].[FM01]),(N'FM02',[Rishabh].[dbo].[Unpvt_test].[FM02]),(N'FM03',[Rishabh].[dbo].[Unpvt_test].[FM03])))

As per what I Observe , I do not see any direct impact of this filter on performance but still curious about its usage.

Thank you very much

Aaron Bertrand 2016-08-24 04:14:05

Possibly a blind spot in the optimizer when using UNPIVOT, or a protection of some kind, but you can get the same results without the filter by using slightly more tedious syntax:

SELECT u.id, Rate = CASE p.c
    WHEN 'FM01' THEN u.FM01
    WHEN 'FM02' THEN u.FM02
    WHEN 'FM03' THEN u.FM03 END,
  RateMonth = p.c 
FROM dbo.Unpvt_test AS u
CROSS JOIN (SELECT 'FM01' 
  UNION ALL SELECT 'FM02' 
  UNION ALL SELECT 'FM03') AS p(c);

Rishabh K 2016-08-24 04:26:57

Thank you for your response Aaron but I have seen unpivot performing better than the CASE expression(for a large data set, in millions).
Did you observe the same performance ??

Aaron Bertrand 2016-08-25 01:17:47

Do you have an actual example where the performance is different? These should optimize roughly the same.

August 8, 2016

No Comments