Cost For Implicit Conversion of Datetime to/from Smalldatetime?

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  February 17, 2011 at 12:10 pm

  #231408

  I wanted to find out if there is any overhead involved in a possible implicit conversion if I compare a datetime and a smalldatetime. I think I've shown

• Comparing a character date and a datetime or smalldatetime date does cause an implicit convert
• Comparing a datetime and a smalldatetime does NOT cause an implicit convert
• ALSO it LOOKS like the implicit convert had no overhead (at least in the tested case)

Can anyone confirm or refute my logic and/or conclusions?

The process:

I created a temp table and populated it using my calendar table. The calendar table contains 1 row for each day from 1/1/1980 to 1/1/2079:

create table #implicit (cDate char(8),dtDate datetime,sdtDate smalldatetime)

insert into #implicit select convert(char(8),datege,112),datege,datege from mylibrary.dbo.calendardst

Temp table #implicit now contains 36161 rows. Then I fired up profiler and noodled around to only show my spid-- and ran the 3 queries shown below to see if a smalldatetime/datetime comparison causes an implicit convert.

The queries compare

• character date to datetime date
• character date to smalldatetime date
• datetime date to smalldatetime date

QUERY: select case when cdate = dtdate then 'c eq dt' else 'c ne dt' end comp1 from #implicit

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

-------- ------ ------ ------ ---------- --------- -------- ------------- ------------ ---------- ----------- ---------- ---------------- ---------- -------- ---- -------- ------------------

Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[dtDate] THEN 'c eq dt' ELSE 'c ne dt' END)) 0 0 Compute Scalar Compute Scalar DEFINE: ([Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[dtDate] THEN 'c eq dt' ELSE 'c ne dt' END) [Expr1004]=CASE WHEN CONVERT_IMPLICIT(datetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[dtDate] THEN 'c eq dt' ELSE 'c ne dt' END 36161 0 0.0036161 14 0.142231 [Expr1004] PLAN_ROW 0 1

|--Table Scan(OBJECT: ([tempdb].[dbo].[#implicit])) 0 1 0 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#implicit]) [tempdb].[dbo].[#implicit].[cDate], [tempdb].[dbo].[#implicit].[dtDate] 36161 0.0987591 0.0398556 23 0.138615 [tempdb].[dbo].[#implicit].[cDate], [tempdb].[dbo].[#implicit].[dtDate] PLAN_ROW 0 1

QUERY: select case when cdate = sdtdate then 'c eq sdt' else 'c ne sdt' end comp2 from #implicit

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

-------- ------ ------ ------ ---------- --------- -------- ------------- ------------ ---------- ----------- ---------- ---------------- ---------- -------- ---- -------- ------------------

Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN CONVERT_IMPLICIT(smalldatetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'c eq sdt' ELSE 'c ne sdt' END)) 0 0 Compute Scalar Compute Scalar DEFINE: ([Expr1004]=CASE WHEN CONVERT_IMPLICIT(smalldatetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'c eq sdt' ELSE 'c ne sdt' END) [Expr1004]=CASE WHEN CONVERT_IMPLICIT(smalldatetime,[tempdb].[dbo].[#implicit].[cDate],0)=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'c eq sdt' ELSE 'c ne sdt' END 36161 0 0.0036161 15 0.142231 [Expr1004] PLAN_ROW 0 1

|--Table Scan(OBJECT: ([tempdb].[dbo].[#implicit])) 0 1 0 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#implicit]) [tempdb].[dbo].[#implicit].[cDate], [tempdb].[dbo].[#implicit].[sdtDate] 36161 0.0987591 0.0398556 19 0.138615 [tempdb].[dbo].[#implicit].[cDate], [tempdb].[dbo].[#implicit].[sdtDate] PLAN_ROW 0 1

QUERY: select case when dtdate = sdtdate then 'dt eq sdt' else 'dt ne sdt' end comp3 from #implicit

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

-------- ------ ------ ------ ---------- --------- -------- ------------- ------------ ---------- ----------- ---------- ---------------- ---------- -------- ---- -------- ------------------

Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN [tempdb].[dbo].[#implicit].[dtDate]=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'dt eq sdt' ELSE 'dt ne sdt' END)) 0 0 Compute Scalar Compute Scalar DEFINE: ([Expr1004]=CASE WHEN [tempdb].[dbo].[#implicit].[dtDate]=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'dt eq sdt' ELSE 'dt ne sdt' END) [Expr1004]=CASE WHEN [tempdb].[dbo].[#implicit].[dtDate]=[tempdb].[dbo].[#implicit].[sdtDate] THEN 'dt eq sdt' ELSE 'dt ne sdt' END 36161 0 0.0036161 15 0.142231 [Expr1004] PLAN_ROW 0 1

|--Table Scan(OBJECT: ([tempdb].[dbo].[#implicit])) 0 1 0 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#implicit]) [tempdb].[dbo].[#implicit].[dtDate], [tempdb].[dbo].[#implicit].[sdtDate] 36161 0.0987591 0.0398556 19 0.138615 [tempdb].[dbo].[#implicit].[dtDate], [tempdb].[dbo].[#implicit].[sdtDate] PLAN_ROW 0 1

---

Cursors are useful if you don't know SQL