Nice question Thomas! I'm seeing more references to SQL check constraints out on the Interwebs than I remember in the past.

Cheers,

Andre Ranieri

I guessed relational and got it wrong, based on the known unknowns such as performance and scalability factors.

+1

+1. Btw the same result occurs for SQL 2012, SQL 2008 R2 and SQL 2005. And probably also SQL 2000 but I don't have access to a...

Nice brain tease figuring out the NOT EXISTS WHERE IS NOT NULL 🙂

But this is more of a general parameter null question, not just limited to the bit data type,...

@link is a varchar(50) variable and you are trying to populate it with multiple rows from your result set. If you only want one result row in the variable...

I agree - Grant Fritchey's books on SQL tuning are the simply amazing.

I own a dog-eared copy of "SQL Server 2008 Query Performance Tuning Distilled" but haven't broken down...

Thanks for the back to basics question! It's nice to return to the basics every once in a while and make sure none of the foundation is slipping away.

Cheers,

Andre...

Nice question - I wonder if the question category should have been table aliases instead of joins 🙂

Thanks,

Andre Ranieri

I answered 1,4,5 on this one.

It was a good refresher experience. Thanks for the question!

Andre

Thanks for the great question!

Andre Ranieri

if the left side of the equal sign is a calculation, then the WHERE clause is "non sargeable", meaning that the optimizer can't use simple search arguments and must instead...

I concur with the other solution. Including the AND clause "AND o.name <> 'GLUCOSE'" would omit all possibility of results containing the name field value "GLUCOSE"

Cheers,

Andre Ranieri

Todsdad:

I considered this but we do have some late shift employees who modify the account database through the CRM UI while the sproc is being run in the SQL Agent...

MDJ - that's a thought I hadn't considered.

Based on my own research (ie Google) I'm experimenting with setting context in the sproc:

SET Context_Info 0x55555

then filtering for the context info...

If I understand your logic correctly, all orders with 'GLUCOSE' field value would be omitted because of the nature of your AND filters.