Thanks for the reply Grant.  I agree using this data must be used with caution.  My goal now is to make the stored results more aware of these situations that...

RLS and other tenant isolation will be in place. Thanks for the comment.

Thanks Jo.  The decision to move to multitenant was made by the IT business leaders rather than the DBA staff.  The decision has merit, but the usual backup/restore methods can't...

Following up on this, we gave up trying to pinpoint what query/queries were causing the issues.  Threw 64 GB of RAM at it which resolved the issue.  We spotted lots...

You mention a few metrics on performance for this method.  Do you also have metrics to compare against the merge statement? I'd be interested to see the IO statistics on...

You could add a date dimension table to your model.  Week count for the year/month/quarter and many others are already in the table, you would have to join to it...

Not sure what you mean by "directly", I think that's what I'm asking for.

The sale_result table is only to show you what the sale table would like if the orders...

Yes, null ship_date arrives in the sale table before sale_order occurs.

You're correct on the denormalization. The goal is to simplify incoming oltp data by giving the ability to update the status of a particular item (backordered, shipped, delivered, event1, event2...

Responding to the solution by scdecade, the null values in the sale table must be populated before the sale_order records can be applied.

This is for a data warehouse so it would be a batch process. Millions of orders to build it initially and then 100k daily.

Thanks for suggestion.  I've edited the script and added a result table.

Your select statement needs quotes wrapped around the @fltr parameter.  Should look like this.

set @qry = 'select * from mydb.dbo.tmptable where color = ''' + @fltr + ''''

When you are...

Use the "Include Live Query Statistics" in SSMS.  It will show the plan even if the query fails from memory exception.

Availability Groups are part of High Availability disaster recovery.  Which promises high, not perfect, sql server availability.