HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Having hard time understanding expressions in below query plan

Submitted by: @import:stackexchange-dba··
0
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'))

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.

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 6


So final query turns out to be like below..

select 
* from 
table
where validto>Expr1014 
and validto <null


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

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 6
select 
* from 
table
where validto>Expr1014 
and validto <null

Context

StackExchange Database Administrators Q#128526, answer score: 3

Revisions (0)

No revisions yet.