patternsqlMinor
Having hard time understanding expressions in below query plan
Viewed 0 times
understandingqueryhavinghardexpressionstimeplanbelow
Problem
I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause
which returned below query plan
```
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
1 1 select * from Employee for system_time as of '2016-02-07 15:39:02.10' 1 1 0 NULL NULL NULL NULL 2 NULL NULL NULL 0.0065729 NULL NULL SELECT 0 NULL
1 1 |--Concatenation 1 2 1 Concatenation Concatenation NULL [Union1005] = ([sql2016].[dbo].[Employee].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID]), [Union1006] = ([sql2016].[dbo].[Employee].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name]), [Union1007] = ([sql2016].[dbo].[Employee].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position]), [Union1008] = ([sql2016].[dbo].[Employee].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department]), [Union1009] = ([sql2016].[dbo].[Employee].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address]), [Union1010] = ([sql2016].[dbo].[Employee].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary]), [Union1011] = ([sql2016].[dbo].[Employee].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]), [Union1012] = ([sql2016].[dbo].[Employee].[ValidTo], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo]) 2 0 2E-07 1266 0.0065729 [Union1005], [Union1006], [Union1007], [Union1008], [Union1009], [Union1010], [Union1011], [Union1012] NULL PLAN_ROW 0 1
0 1 |--Clustered Index Scan(OBJECT:([sql2016].[dbo].[Employee].[PK__Employee__7AD04FF1C19A16B5]), WHERE:([sql2016].[dbo].[Employee].[ValidFrom]'2016-02-07 15:39:02.1000000'))
which returned below query plan
```
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
1 1 select * from Employee for system_time as of '2016-02-07 15:39:02.10' 1 1 0 NULL NULL NULL NULL 2 NULL NULL NULL 0.0065729 NULL NULL SELECT 0 NULL
1 1 |--Concatenation 1 2 1 Concatenation Concatenation NULL [Union1005] = ([sql2016].[dbo].[Employee].[EmployeeID], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[EmployeeID]), [Union1006] = ([sql2016].[dbo].[Employee].[Name], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Name]), [Union1007] = ([sql2016].[dbo].[Employee].[Position], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Position]), [Union1008] = ([sql2016].[dbo].[Employee].[Department], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Department]), [Union1009] = ([sql2016].[dbo].[Employee].[Address], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[Address]), [Union1010] = ([sql2016].[dbo].[Employee].[AnnualSalary], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[AnnualSalary]), [Union1011] = ([sql2016].[dbo].[Employee].[ValidFrom], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidFrom]), [Union1012] = ([sql2016].[dbo].[Employee].[ValidTo], [sql2016].[dbo].[MSSQL_TemporalHistoryFor_565577053].[ValidTo]) 2 0 2E-07 1266 0.0065729 [Union1005], [Union1006], [Union1007], [Union1008], [Union1009], [Union1010], [Union1011], [Union1012] NULL PLAN_ROW 0 1
0 1 |--Clustered Index Scan(OBJECT:([sql2016].[dbo].[Employee].[PK__Employee__7AD04FF1C19A16B5]), WHERE:([sql2016].[dbo].[Employee].[ValidFrom]'2016-02-07 15:39:02.1000000'))
Solution
Thanks Kin for pointing to Paul White's article. Earlier wherever I have scalar operators I used to see them assigned some value,but in this case,those values are calculated from below function.
So scalar operator
So final query turns out to be like below..
As per Article,these values are there to convert data types which don't match,but in my case those columns are created by SQL server and I don't insert any data.
References:
https://sqlkiwi.blogspot.com/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html
GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))So scalar operator
Expr1014 is assigned--2016-02-07 15:39:02.1000000
Expr1015 is assigned null
Expr1013 is some internal value here 6So final query turns out to be like below..
select
* from
table
where validto>Expr1014
and validto <nullAs per Article,these values are there to convert data types which don't match,but in my case those columns are created by SQL server and I don't insert any data.
References:
https://sqlkiwi.blogspot.com/2012/01/dynamic-seeks-and-hidden-implicit-conversions.html
Code Snippets
GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6))Expr1014 is assigned--2016-02-07 15:39:02.1000000
Expr1015 is assigned null
Expr1013 is some internal value here 6select
* from
table
where validto>Expr1014
and validto <nullContext
StackExchange Database Administrators Q#128526, answer score: 3
Revisions (0)
No revisions yet.