OR Operator Short-circuit in SQL Server

sql-server tsql query-optimization short-circuiting
NotTwoWayStreet picture NotTwoWayStreet · Jun 27, 2012 · Viewed 13.3k times · Source

I want to consult SQL Server OR short-circuit

Code: 

   DECLARE @tempTable table
      (
         id int
      )
      INSERT @tempTable(id) values(1)

      DECLARE @id varchar(10)
      SET @id = 'x'
      SELECT * FROM @tempTable WHERE 1=1 OR id = @id --successfully
      SELECT * FROM @tempTable WHERE @id = 'x' OR id = @id --Exception not Convert 'x' to int

Why? 1=1 and @id='x' is true.

SQL Server OR operator : whether the short-circuit function？

THANKS

Answer

Steven Mastandrea picture Steven Mastandrea · Jun 27, 2012

Within SQL, there is no requirement that an OR clause breaks early. In other words, it is up to the optimizer whether to check both conditions simutaneously. I am not an expert in the MSSQL optimizer, but I have seen instances where the optimizer has and has not short circuited an OR clause.

Related questions

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two …

Read More
What is the optimal way to compare dates in Microsoft SQL server?

I have a SQL datetime field in a very large table. It's indexed and needs to be queried. The problem is that SQL always stores the time component (even though it's always midnight), but the searches are to the day, …

Read More
T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed …

Read More