Yesterday in the Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly post I showed you that sp_executesql is better than exec because you get plan reuse and the procedure cache doesn’t get bloated.

Today I will show you that sp_executesql is better than exec or ad hoc queries when you deal with conversions in execution plans

First create this table and populate it with some data

  create table TestPerf(	id int not null,
        SomeCol1 nvarchar(20),
        SomeValue smallint)

  insert TestPerf values(1,'Test',1)
  insert TestPerf values(2,'Aest',257)
  insert TestPerf values(3,'Best',258)
  insert TestPerf values(4,'Cest',259)
  insert TestPerf values(5,'Dest',251)
  insert TestPerf values(6,'Eest',252)
  insert TestPerf values(7,'Fest',253)
  insert TestPerf values(8,'Gest',254)
  insert TestPerf values(9,'Hest',255)

Take a look this query

select * from TestPerf
where SomeCol1 = 'Test'
and SomeValue = 1

Run the query

Here is the execution plan

_
   
|–Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
   
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0)
   
AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))_

As you can see the value 1 needs to be converted to smallint and ‘Test’ Needs to be converted to nvarchar. You can actually see what the value is converted to by using the SQL_VARIANT_PROPERTY function.

When you run the following query

  SELECT SQL_VARIANT_PROPERTY(1, 'BaseType') AS [Base Type],
        SQL_VARIANT_PROPERTY(1, 'Precision') AS [PRECISION],
        SQL_VARIANT_PROPERTY(1, 'Scale') AS [Scale]

  union all
  SELECT SQL_VARIANT_PROPERTY('Test', 'BaseType') AS [Base Type],
        SQL_VARIANT_PROPERTY('Test', 'Precision') AS [PRECISION],
        SQL_VARIANT_PROPERTY('Test', 'Scale') AS [Scale]

you get this as output

  Base Type	PRECISION	Scale
  int	        10	        0
  varchar	        0	        0

so the value 1 becomes an int and ‘test’ is varchar

Exec with dynamic SQL is not any better than ad hoc of course since the same query gets generated as with the ad hoc query

Here is such a query

declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'

declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = ''' + @SomeCol1 +'''
and SomeValue = ' + convert(nvarchar(10),@SomeValue) 

exec (@SQL)

Here is the execution plan

|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0) 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))
 

Ideally you want a query with parameters; below is the quey

declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'

select * from dbo.TestPerf
where SomeCol1 =  @SomeCol1 
and SomeValue = @SomeValue 

Here is the execution plan

|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))

As you can see no conversions here

Now let’s take a look at how we can take the dynamic query from before and use sp_executesql to get rid of conversions

Run this query

declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1 
and SomeValue =  @InnerSomeValue' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'



exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerSomeValue = @SomeValue,
			@InnerSomeCol1 	= @SomeCol1
      

Here is the execution plan

As you can see there were no conversions.

How to avoid conversions

The thing that is important with a query like that is that the parameters inside the dynamic sql match the data types of the columns.

So for this part of the query

select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1 
and SomeValue =  @InnerSomeValue' 

The SomeCol1 column and the @InnerSomeCol1 param/variable have to be of the same datatype,
the SomeValue column and the @InnerSomeValue param/variable also have to be of the same datatype in order to prevent conversions

Let’s look at something else
The query from before with parameters

declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'

select * from dbo.TestPerf
where SomeCol1 =  @SomeCol1 
and SomeValue = @SomeValue 

Here is the execution plan

 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))
 

what happens if you change the params to this

declare @SomeValue int
declare @SomeCol1 varchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'

select * from dbo.TestPerf
where SomeCol1 =  @SomeCol1 
and SomeValue = @SomeValue 

Here is the execution plan

 |--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), 
 WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] 
 AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(20),[@SomeCol1],0)))

We still get a conversion on the nvarchar SomeCol1 column

What happens if we change the outer parameter data types in the following dynamic query?

declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1 
and SomeValue =  @InnerSomeValue' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'

exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerSomeValue = @SomeValue,
			@InnerSomeCol1 	= @SomeCol1

Here is the execution plan

Now we will change the parameters from smallint and nvarchar to int and varchar in the query below. This won't make any difference in the execution plan because the parameters used in the plan are still of the correct datatype since the inner parameters are used not the outer ones!!

/*changed from
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)*/

declare @SomeValue int
declare @SomeCol1 varchar(20)

select @SomeValue = 1,@SomeCol1 = 'Test'


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1 
and SomeValue =  @InnerSomeValue' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'



exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerSomeValue = @SomeValue,
			@InnerSomeCol1 	= @SomeCol1

Here is the execution plan

That wraps up this blog post

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum