Automatic SQL Server query execution optimization: "get of my lawn"

 

I had to write a SQL query, that would return the records where the column "ImportFileName" started with the year "2013"

 

-- Drop temp table if it exists.
if object_id('tempdb..#Import') is not null
begin
    drop table #Import
end

-- Create temp table.
create table #Import
(
    Id int not null,
    ImportFileName varchar (255) not null
)

-- Seed temp table.
insert into #Import (Id, ImportFileName) values 
(1, '20130506101010.csv'),
(2, '20130606101010.csv'),
(3, 'This_is_an_error_file.csv'),
(4, '20130806101010.csv'),
(5, '20130906101010.csv')

-- Query the temp table
select  Id
        ImportFileName
from    #Import
where   datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013

-- Drop temp table.
drop table #Import

This query results in the error:

Msg 241, Level 16, State 1, Line 19
Conversion failed when converting date and/or time from character string.

This can be fixed with the SQL Server 2012 and above function: TRY_CONVERT:

-- The fixed query (>= SQL Server 2012)
select    Id
        ImportFileName
from    #Import
where    datepart(year, try_convert(date, substring(ImportFileName, 1, 8))) = 2013

But in SQL Server 2008R2 this function does not exist, so I tried to trick SQL Server by using a subquery that would only return valid records:

-- Query the temp table
select  i.Id,
        i.ImportFileName
from    (
    select    Id,
            ImportFileName
    from    #Import
    where    isdate(substring(ImportFileName, 1, 8)) = 1
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013

But this resulted in the same error, why????

Well the estimated execution plan tells you why:

 

 

Effectively SQL Server query optimization will merge the two where statements and convert the query above to something like:

 

select    Id,
            ImportFileName
    from    #Import
    where    datepart(year, cast(substring(ImportFileName, 1, 8) as date)) = 2013
    and     isdate(substring(ImportFileName, 1, 8)) = 1

which will fail.

 

To get around this problem, you could first insert the subquery records in a temp table and then check query the temp table, but in my case I used a case statement:

-- Query the temp table
select  i.Id,
        i.ImportFileName
from    (
    select    Id,
            case    when isdate(substring(ImportFileName, 1, 8)) = 1
                    then    ImportFileName
                    else    null
            end as ImportFileName
    from    #Import
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013

The full script now look like:

-- Drop temp table if it exists.
if object_id('tempdb..#Import') is not null
begin
    drop table #Import
end

-- Create temp table.
create table #Import
(
    Id int not null,
    ImportFileName varchar (255) not null
)

-- Seed temp table.
insert into #Import (Id, ImportFileName) values 
(1, '20130506101010.csv'),
(2, '20130606101010.csv'),
(3, 'This_is_an_error_file.csv'),
(4, '20130806101010.csv'),
(5, '20130906101010.csv')

-- Query the temp table
select  i.Id,
        i.ImportFileName
from    (
    select    Id,
            case    when isdate(substring(ImportFileName, 1, 8)) = 1
                    then    ImportFileName
                    else    null
            end as ImportFileName
    from    #Import
) i
where   datepart(year, cast(substring(i.ImportFileName, 1, 8) as date)) = 2013

-- Drop temp table.
drop table #Import