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.

Advertisements

About Marcos Freccia

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

Posted on July 12, 2011, in T-SQL Tuesday, VirtualPass and tagged , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: