T-SQL condition short-circuiting does not work

This isn't really a WPF issue at all - it concerns SQL Server 2012.

I've always thought that SQL Server (T-SQL in general) uses short-circuiting when evaluating conditions. For example, if you have dates stored in YYYYMMDD format (because they come from an ancient HP-3000) and null dates are stored as '00000000' then you can search on the field like this...

DATE != '00000000' AND CAST(DATE AS DateTime) > '1/1/2015'

Even though you cannot cast '00000000' as a datetime this works because the invalid dates never get to the CAST function because of short-circuiting. This is the same as the VB.Net AndAlso operator.

An employee came to me today because he was getting an invalid cast exception using syntax like this. His SELECT statement was part of a cursor declaration which was inside a function. I extracted the SELECT statement and ran it in a new query and it ran fine. The problem only occurred when we ran it as part of a cursor declaration.

To test my theory that failure of short-circuiting was the cause of the problem I altered the SQL to look more like this...

CAST(CASE WHEN DATE='00000000' THEN '19000101' ELSE DATE END) > '1/1/2015'

The modified function works correctly which confirms my theory that you cannot rely on short-circuiting in SQL Server. Clearly the statement was being optimized differently when part of a cursor. Also, the function was working fine a few days ago so either we got a patch that broke it or perhaps the statistics changed enough to change the optimization plan for the statement.

I've done a little research on-line and I've found some authoritative blogs that state "SQL Server supports expression short circuiting but you cannot rely on the sequence the expressions will be evaluated." In effect this improves performance but you probably want to use a CASE statement if you have a condition like the one described in this blog.