Unpivot implementation with NOT NULL COlumn
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
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);
2016-08-25 01:17:47
Do you have an actual example where the performance is different? These should optimize roughly the same.
Did you observe the same performance ??