I wonder if this is a simple as putting a constraint on zip. Will test tomorrow.

1) Strangely, I had just created the clustered index (zip,pin) on the table so it should have had 100% knowledge (statistics) of that field (e.g. the range that it is...

Actual plans.

Looks like this is just cost-based optimization, which is interesting but somewhat surprising.

I'll see about posting actual plans later today. The table these queries are going against has 177 columns, the majority of which could potentially be used in the WHERE clause....

Added sqlplan as requested (with some name changes).

The table is roughly 68 GB if that helps (this is with page compression).

Looking at both plans in sql sentry plan explorer, I...

I'll have to see if the higher ups are okay with me posting that.

Definitely not blocking because this is a test database so no other queries are running against it...

Valid points, I was just exploring an alternative. No offense intended!

use master

go

exec sp_msforeachdb 'USE ?;if not exists (select 1 from sys.databases where owner_sid = 0x01 and name = db_name()) exec sp_changedbowner ''sa'''

Is the column SUBORDER_ID defined the same way in both tables?

Both filters in the WHERE clause refer to the smaller table, so they won't impact performance so much. I would recommend creating the following index on the development server and...

From what information you have given, I believe you should look at indexing for this.

At a minimum, you would probably want indexes with the join columns as the left-most column...

SKYBVI (10/17/2011)

---

I guess the answer is wrong

It should be 1 and 2 only

beacuse only the constraint is violated,

the sql statement is terminated.

it doesn't look for other statements...

just goes out of...