SQL Server

Implicit conversions

Or ‘How to slow down a query without apparent reason’

I’ve discussed functions in where clauses before and shown how they can prevent index usage and slow down queries. I’ve also discussed data type conversions and shown that SQL Server sometimes does the conversions in places not expected. There’s a nice little gotcha that results from the combination of these two. Let’s take a look at a simple example.

Which of the following queries will run slower? (sample code at the end)

Select ID from TestingConversion where Code = 'AAA'
Select ID from TestingConversion where Code = N'AAA'


Order of execution

Or “Which where runs when?

There seems to be a lot of misunderstanding about what order conditions within a where clause are executed. In two days I heard from three different places comments about what order conditions must be put in the where clause.

The simple truth is that is doesn’t matter. Let’s look at a very simple example. (Sample code at the end as always)

SELECT * from tblTestWhereOrder WHERE A=1 and B=2 and C is not null


Functions in a where clause

Or ‘How to really slow a query down without trying’

Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post.

-- returns 5 out of 5000 rows
SELECT InsertDate FROM TestingFunction WHERE LEFT(Code,1)='AA'

Knowing that there’s an index on the column code, the optimiser might be expected to use an index seek to satisfy the query. However, the execution plan shows an index scan. Why?


Data Conversion Woes, pt 2

In Part 1 I discussed why some queries that look like they should work fail with a conversion error. In this part, I’m going to go over some solutions to the problem.

  1. Probably best solution, at least in the long term is, as I mentioned previously, to correct the data type of the column and to remove the data that doesn’t fit. This is probably, however, the hardest of the solutions to implement, due to the potential impact.


Data Conversion Woes

Or “I’m filtering on ISDATE(dt) = 1. Why am I still getting conversion errors?

Well, to put it simply, because SQL’s evaluating the where clause in a different order to what you may have expected.

Let’s try a simple example.

SELECT * FROM tbl_TestingConversion
WHERE ISDate(TheDate)=1
 AND CAST(TheDate AS DATETIME) > '2007/07/01'


An overview of Execution plans

I was asked a question about this recently, so I thought I’d start this blog off with a brief overview of SQL Server’s execution plans.

Simply put, a query’s execution plan is the query translated into a hierarchical set of operators that the query execution engine understands. The query optimiser is responsible for producing this plan.

A query can have a number of different possible execution plans, depending on the resources available on the server, the amount of data in the source tables, the load on the server.