The original version I wrote of this was in 2006 for Experts Exchange, and it was to strip nonnumeric chars (although of course the basic idea is the same):

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_21957163.html

Of course...

Can we assume the large tables have indexes keyed on the IDs that are being passed in?

If so, and the IDs are in a (fairly) tight range, you could also...

--Probably easiest would be to put the control values into a separate table, and if necessary use exclusive locks on that table when seeding that table.

CREATE TABLE dbo.control_table (

...

No, changing the column size requires full logging for whatever logging it needs.

But note that increasing a varchar(nnnn) column should only change catalog data, not the data pages, so it...

No, because non-materialized views don't occupy data space in the db. Only the view definition is stored; the data is generated from the underlying tables when the view is...

FWIW, my code for computing max possible row length returns a length of 11870 for that table.

Yes. Honestly, it's because that's just the way SQL works.

If a row doesn't fit, SQL will look for any (MAX) columns that are currently being stored in the row....

ross.mason 49698 (9/29/2014)

---

The only way the vendor will send us our data is a full back up in the morning with logs every hour. I have resigned myself to restoring...

Another possibility might be to use a differential backup to do the forward recovery rather than applying logs, particularly if the log process is slow.

Take a differential backup on the...

While this will slow down processing of the value(s) somewhat, change the datatype of one (or more) of the longest columns -- [UserName_vc], [OfficeEmailAddress_vc], [HomeEmailAddress_vc] -- from nvarchar(320) to nvarchar(max)....

You can use CROSS APPLY to effectively assign an alias to an expression and then use it in any SQL clause:

SELECT test + ...,

FROM ...

CROSS APPLY (

...

Try this:

SELECT COALESCE(fs.CustomerKey, fo.CustomerKey) AS CustomerKey

, COALESCE(fs.StoreKey, fo.StoreKey) AS StoreKey

, ISNULL(fs.LastWeek, 0) + ISNULL(fo.LastWeek, 0) AS LastWeek

, ISNULL(fs.LastTwoWeeks, 0) + ISNULL(fo.LastTwoWeeks, 0) AS LastTwoWeeks

, ISNULL(fs.LastThreeWeeks, 0) + ISNULL(fo.LastThreeWeeks, 0) AS...

No, such detailed change log info is not available. And when you think about it, you realize that it would be just too much overhead and disk space for...

I don't think you need to go thru all that. Just modify the original query to get what you need. Not a lot of details, but something like...

About the best you can do is to cluster the Costing table by date. That could help significantly when there are a limited number of lookups. For large...