In addition to the other suggestions, make sure that:

1) Join columns are indexed

2) Indexes have the proper freespace and are reorganized / rebuilt as often as needed to keep them...

It looks to me like SQL is using the non-clus index on the lookup table. It scans it once for each LOJ, building a hash table to match to...

I agree about avoiding the concatenation. And it's definitely not needed here anyway:

SELECT *

FROM @tbl1 tbl1

INNER...

Add this column:

IND_ACTIVO

as an INCLUDEd column to your existing index.

Otherwise, the query plan looks reasonable.

It would be better if you didn't convert for the sort, but you will get...

Yes, definitely increase the RAM for the SQL server if you can.

Also, if possible isolate the physical db files on their own drives, and increase the read part of the...

Is there enough available space in the filegroup in which you intend to place this index? If not, you should probably allocate more space to it yourself beforehand rather...

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...