SQL Server - Query Short-Circuiting?

Question

Do T-SQL queries in SQL Server support short-circuiting?

For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not have leading zeros (might be "000000001234" or "1234").

So my query to match the two is something like: select * from table1 where table1.ID LIKE '%1234'

To speed things up, I'm thinking of adding an OR before the like that just says: table1.ID = table2.ID to handle the case where both ID's have the padded zeros and are equal.

Will doing so speed up the query by matching items on the "=" and not evaluating the LIKE for every single row (will it short circuit and skip the LIKE)?

Answer 1

fix the database to be consistent

select * from table1 where table1.ID LIKE '%1234'

will match '1234', '01234', '00000000001234', but also '999991234'. Using LIKE pretty much guarantees an index scan (assuming table1.ID is indexed!). Cleaning up the data will improve performance significantly.

if cleaning up the data is not possible, write a user-defined function (UDF) to strip off leading zeros, e.g.

select * from table1 where dbo.udfStripLeadingZeros(table1.ID) = '1234'

this may not improve performance (since the function will have to run for each row) but it will eliminate false matches and make the intent of the query more obvious

EDIT: Tom H's suggestion to CAST to an integer would be best, if that is possible.

Answer 2

If the ID is purely numeric (as your example), I would reccomend (if possible) changing that field to a number type instead. If the database is allready in use it might be hard to change the type though.

Answer 3

You want to make sure that at least one of the tables is using its actual data type for the IDs and that it can use an index seek if possible. It depends on the selectivity of your query and the rate of matches though to determine which one should be converted to the other. If you know that you have to scan through the entire first table, then you can't use a seek anyway and you should convert that ID to the data type of the other table.

To make sure that you can use indexes, also avoid LIKE. As an example, it's much better to have:

WHERE
     T1.ID = CAST(T2.ID AS VARCHAR) OR
     T1.ID = RIGHT('0000000000' + CAST(T2.ID AS VARCHAR), 10)

than:

WHERE
     T1.ID LIKE '%' + CAST(T2.ID AS VARCHAR)

As Steven A. Lowe mentioned, the second query might be inaccurate as well.

If you are going to be using all of the rows from T1 though (in other words a LEFT OUTER JOIN to T2) then you might be better off with:

WHERE
     CAST(T1.ID AS INT) = T2.ID

Do some query plans with each method if you're not sure and see what works best.

The absolute best route to go though is as others have suggested and change the data type of the tables to match if that's at all possible. Even if you can't do it before this project is due, put it on your "to do" list for the near future.

Answer 4

You could add a computed column to the table. Then, index the computed column and use that column in the join.

Ex:

Alter Table Table1 Add PaddedId As Right('000000000000' + Id, 12)
Create Index idx_WhateverIndexNameYouWant On Table1(PaddedId)

Then your query would be...

select * from table1 where table1.PaddedID ='000000001234'

This will use the index you just created to quickly return the row.

Answer 5

How about,

table1WithZero.ID = REPLICATE('0', 12-len(table2.ID))+table2.ID

In this case, it should able to use the index on the table1

Answer 6

SQL Server does NOT short circuit where conditions. it can't since it's a cost based system: How SQL Server short-circuits WHERE condition evaluation .

Answer 7

Just in case it's useful, as the linked page in Mladen Prajdic's anwer explains, CASE clauses are short-circuit evaluated.