To be sure of the...

If it's varchar, best would be to test it as varchar, thus not converting the column ... but that is only safe if all values are consistently left-padded with zero(s).

Either...

Format 'YYYYMMDD hh:mm' is always accurately translated in SQL Server.

Therefore, you need just two STUFFs:

SELECT

varchar_data AS original_string,

CAST(STUFF(STUFF(varchar_data, 9, 0, ' '),...

True, if they actually are using SQL 2012. Sometimes you can't be sure :-D.

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 (

...