Боремся с неявным приведением типов в SQL Server

http://www.sql.ru/blogs/decolores/1112

Автор: DeColo®es

Неявное приведение типов данных, которое в определенных случаях допускается в T-SQL - очень удобная штука. Для новичка. С приобретением опыта, разработчики стараются держать все под контролем и чаще скорее сами стараются определить, каким образом значение должно преобразовываться из одного типа в другой. Дело дошло до того, что возможность полного запрета на неявную конвертацию регулярно запрашивается сообществом пользователей SQL Server у разработчиков...
Чем же так плохо наличие вроде бы очень удобного, прощающего ошибки и сокращающего код функционала СУБД?

Рассмотрим пример:

declare @detail table(ID varchar(10) primary key, masterID varchar(10))

declare 

        @ID_str varchar(10) = '1',

        @ID_int int = 1

select * 

from @detail 

where ID = @ID_str

select * 

from @detail 

where ID = @ID_int

Во втором запросе будет использоваться неявное приведение типов для сопоставления числовой переменной и строкового поля.

Итак, проблемы, вызываемые неявным приведением типов:
(конечно, проблемы возникают не из-за того, что это приведение неявное, но в случае явного приведения типов, разработчик скорее всего либо сознательно их обойдет, либо обнаружит причину проблемы достаточно быстро)

1. Производительность
Если посмотреть планы выполнения запросов из примера, то можно увидеть, что индекс по первичному ключу будет использован в первом запросе и не будет - во втором. (Использовался SQL Server 2008 R2 build 1600, в других версиях планы выполнения могут быть другие)
Причина в том, что в данном случае сервер принял решение приводить значение поля к типу данных int, что привело к необходимости сканирования всех значений данного поля. (Поиск по индексу возможен тогда, когда нужно найти конкретное значение, которое заранее неизвестно в случае необходимости преобразования проиндексированных значений).
В реальных базах мне встречались случаи, когда вроде бы подходящие индексы не использовались из-за приведения типов int<>smallint или varchar<>nvarchar

2. Ошибки
Если в таблицу @detail примера вставить значение ID, которое нельзя привести к типу int, то второй запрос завершится с ошибкой:

insert into @detail values('a', 'b')

select * 

from @detail 

where ID = @ID_int

Msg 245, Level 16, State 1, Line 12 Conversion failed when converting the varchar value 'a' to data type int.

В данном случае не поможет даже фильтрация записей, в поле ID которых содержатся только цифры - сервер может сам определять порядок применения операторов.

Причем подобного рода ошибки возникают, как правило, уже после внедрения системы в эксплуатацию и догадаться, в чем именно проблема бывает сложно.

Для того, чтобы найти подобного рода неявные преобразования в планах выполнения запросов, находящихся в процедурном кэше SQL Server, можно воспользоваться запросом.

Предупреждение

Данный запрос может выполняться долго (до 10 минут и дольше) в зависимости от количества и сложности запросов в процедурном кэше, мощности и загруженности сервера. При этом он создает некоторую дополнительную нагрузку на процессор, поэтому использовать его в промышленной среде следует с осторожностью. Автор не несет ответственность за любые последствия, включая материальные взыскания применившим этот запрос в неудачное время в неудачном месте

 

set ansi_nulls on

go

select 

        db_name(st.dbid), 

        object_schema_name(st.objectid, st.dbid) +'.'+object_name(st.objectid, st.dbid),

        case when sql_handle IS NULL then ' '

               else ( substring(st.text,(qs.statement_start_offset+2)/2,(

               case when qs.statement_end_offset = -1        

                       then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )

        end as query_text, 

        creation_time,

        last_execution_time,

        execution_count,

        total_worker_time/1000 as CPU,

        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],

        qs.total_elapsed_time/1000 as TotDuration,

        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],

        total_logical_reads as [Reads],

        total_logical_writes as [Writes],

        total_logical_reads+total_logical_writes as [AggIO],

        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],

        convert(xml, '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1"><BatchSequence><Batch><Statements>'

               +convert(varchar(max), sub_query)+'</Statements></Batch></BatchSequence></ShowPlanXML>') as query_plan

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) st

outer apply (

        select 

               Stmt.node.query('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; .') sub_query

        from sys.dm_exec_query_plan(qs.plan_handle) pl

        cross apply pl.query_plan.nodes('//*[local-name()="StmtSimple"]') Stmt(node)

        where convert(varbinary(20), Stmt.node.value('@QueryHash', 'varchar(20)'), 1) = qs.query_hash

                and Stmt.node.exist('//*[local-name()="Convert" and @Implicit = "1"]') = 1

        ) p

order by total_logical_reads desc

Расшифровка данных, возвращаемых данным запросом - в статье Статистика, собираемая SQL Server по наиболее тяжелым запросам.