SQL Server - short circuit request?

Question

Do T-SQL Queries in SQL Server Support Short Circuit?

For example, I have a situation where I have two databases and I compare the data between two tables to match and copy some information. In one table, the ID field will always have leading zeros (for example, 000000001234), and in another table the identifier field may or may not have leading zeros (it can be 000000001234 or 1234).

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

To speed up the process, I'm going to add OR in front of the like, which simply says: table1.ID = table2.ID to handle the case where both identifiers have filled zeros and are equal.

Will this speed up the query by matching elements in "=" and not evaluating LIKE for each individual line (will it be short and skip LIKE)?

Answer 1

SQL Server does NOT short circuit when conditions. it cannot, because it is a cost-based system: How SQL Server WHERE evaluates short-circuit conditions .

Answer 2

You can add a calculated column to the table. Then index the calculated column and use that column in the join.

Example:

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

Then your request will be ...

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

This will use the newly created index to quickly return the row.

Answer 3

You want to make sure that at least one of the tables uses its actual data type for identifiers and that it can use index search, if possible. It depends on the selectivity of your request and the speed of matches, but to determine which one needs to be converted to another. If you know that you need to scan the entire first table, then you still cannot use the search, and you must convert this identifier to the data type of another table.

To make sure you can use indexes, also avoid LIKE. For example, it is 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 Stephen Low noted, the second request may also be inaccurate.

If you are going to use all the lines from T1, though (in other words, LEFT OUTER JOIN to T2), you might be better off:

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

Make some query plans with each method if you are not sure and see what works best.

The absolute best route to go, like others, has suggested and changed the data type of the tables so that they match, if at all possible. Even if you cannot do this before this project is completed, put it on your "to do" list in the near future.

Answer 4

What about,

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

In this case, it should use the index in table1

Answer 5

Just in case, this is useful, as the linked page in Mladen Prajdic anwer explains, CASE offers are short-circuited.

Answer 6

fix the database so that it is consistent

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

will match "1234", "01234", "00000000001234", but also "999991234". Using LIKE pretty much guarantees index scans (assuming table1.ID is indexed!). Clearing data will greatly improve performance.

if data cleaning is not possible, write a user-defined function (UDF) to remove leading zeros, for example.

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

this may not improve performance (since the function should be run for each line), but it will eliminate false matches and make the purpose of the request more obvious.

EDIT: Tom H CAST's proposal for the whole would be better if possible.

Answer 7

If the identifier is purely numeric (as an example), I would recommend (if possible) changing this field to the type of the number. If the database is already in use, it can be difficult to change the type.