SQL Server - Query Short-Circuiting?

Solution 1:

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 .

Solution 2:

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.

Solution 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.

Solution 4:

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