Look at this in the execution plan...

USE AdventureWorks

GO

SELECT *

FROM HumanResources.Employee

WHERE EmployeeID BETWEEN 100 AND 200

Notice how it rewrote the code? Is that what you're talking about?...

Excellent point about DISTINCT. I see it all the time.

Also, using HAVING to check for conditions that should be in a WHERE. That can have a serious impact...

Again, this code does work:

select isdate(col1), *

from #t

where isdate(col1) = 0

or datepart(year, col1) = 2001 --sql treats yr "1" as "2001"

SQL will definitely short circuit when it knows it...

If SQL Server evaluated left to right, in the above query, it wouldn't have to evaluate the second condition if the first was false (short circuit) because it would not...

The sample temp table code posted doesn't prove what order SQL evaluated the expressions in. With "and" specified, it always has to evaluate both expressions.

Note that this code does...

As I said, it was my understanding that the SQL optimizer did not do that for simple (non-query) expressions connected by boolean operators, that it evaluated left-to-right.

Hmm, don't see much wrong, except maybe:

WHERE ID = @liMaxMaxID + 1

You're checking for a specific ID value, not just the first value greater than the prev max. Is...

I thought SQL Server did always...

That's not practical in this type of situation.

If the log is already bloated because it previously wasn't backed...

What about an approach that used a generic stored proc to dynamically create static code for a specific table?

That is, the code could handle any table name passed to it,...

How many rows are in the temp table?

How many average duplicates are there of each ID, roughly, in each table? That is, what is the value of:

SELECT COUNT(DISTINCT ID)...

I agree. Qods have become so careless and subjective as to be worthless.

As everyone else has noted, wth does "dob" have to do with "join" date?

Because then the exact same check that works...

Wouldn't L.A. fall under CA law?

Where...

But even your own "timeanddate.com" link makes it clear that the official birthdate is Mar 1 in non-leap years:

while others celebrate their birthday on March 1 because they do...