I did include TRUNCATE in the trans, from the start.  Not exactly sure how else you want the transaction structured.

I can understand SQL having to scan the table / index, but I don't see why SQL would need to do a sort.  Btw, an asc index will do, you...

Yes, technically TABLOCKX is only for that statement.  I guess in theory someone could INSERT a row between the SELECT and the TRUNCATE.

I should have added HOLDLOCK to the first...

I have to admit, in this situation I'd likely just use a cursor and a loop, something like below, just because of the complexity, and likely overhead, of recursion in...

Something along these lines:

SELECT TOP (0) *
INTO dbo.maintable_backup
FROM dbo.maintable
--Edit: Added UNION ALL to cancel IDENTITY property, if present
UNION ALL
SELECT TOP (0) *
FROM dbo.maintable

BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.maintable_backup
SELECT...

• This reply was modified 5 years, 6 months ago by ScottPletcher. Reason: Added code to table create, using INTO, to cancel IDENTITY property if there is an IDENTITY column in the table

The max list is easy enough to produce.  Sorry, I don't fully understand the 'OK' part well enough yet to add that to the query.  'OK' just meaning that you...

Would you please provide directly usable data, i.e. CREATE TABLE and INSERT statement(s) for the sample data.  [A splat of data on the screen does us no good to try...

Congrats, very nice article.  Informative and to the point.  And it's so great to see an intersection ("bridge") table without an identity column, instead keyed by the parent's keys, the...

There was very likely different activity on the two servers.  That could cause SQL's missing index recommendations to change, which would in turn likely cause BlitzIndex's recommendations to change.  (Note:...

I think there was a bit of a typo there, CR + LF is (13)+(10), not (10)+(13).  You probably also want to replace single CHAR(10)/CHAR(13) chars, to be safe, like...

No, on a non-partitioned table, there is no way to build chunks / parts of indexes (frankly I think there should be, but there isn't).

Typically people use a NULLIF like below, to avoid a CASE statement -- whether you prefer that or not is, of course, up to you:

ISNULL(((JobInfo.EstHrs / NULLIF(JobHrs.ActualHrs, 0)) * 100),...

If the NULL determination was not a 1-time thing, but needed to be periodically available, I'd use triggers to maintain a counter per column in another table that "told" me...

That's what I assumed it meant. Other interpretations didn't make sense. I suppose writing the table name and column name to at least a temporary table and then...

I'm not sure specifically what the OP meant by "records [presumably meaning rows] for only the columns that have data in them".

Does that mean rows with NO null columns at...