T-SQL Tuesday #20 – T-SQL Best Practices

Hi Guys,

This month the subject for T-SQL Tuesday is “T-SQL Best Practices” chosen by Amit Banerjee (Twitter|Blog) . I will describe here a problem that happened some days ago in a customer, and just changing a little thing in the query made all the difference.

The customer told me that some queries have been running slowly, so my job there, was dig further and find what have been wrong.

I found out that all of these queries have been using “convert implicit” and none of indexes have been used. And just put the value of where clause in quotes the index began to be used. I’ll put some some code below to illustrate the example.

create table #temp ( id int not null, idVendor varchar(100))

go

alter table #temp add constraint PK primary key (id)

go

insert into #temp (id,idVendor)

values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)

go

create nonclustered index ix_teste on #temp(idVendor)

go

select idVendor from #temp

where idVendor = 1

Executing the query we have the execution plan below.

Image1 Image2

As we can see, the nonclustered index didn’t solve my problem because a convert_implict was made to satisfy the query.

To solve this query just put the value of idVendor between quotes like this:

select idVendor from #temp

where idVendor = ‘1’

and we have the follow execution plan:

Image3 Image4

So, just doing a T-SQL best practice we avoided a convert_implicit and an index scan for the query.

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em julho 12, 2011, em T-SQL Tuesday, VirtualPass e marcado como , , , . Adicione o link aos favoritos. 1 comentário.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: