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

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 2

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 .