;WITH cte_1 AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY HH ORDER BY HH) AS row_num
    FROM MyTable
)
SELECT 
   ...

There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column to...

There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column to the...

Actually "<" is NOT a safe way to check for not space.  CR and LF are both not "<" a space.

select case when char(10) > space(1) then 'LF ' else...

References in SQL are to object names.  The code will resolve based on the name.

A rename can't happen while the table is being queried, because a schema lock is in...

I agree.  Why the "need" to jump to such a huge number?

Correct.  You'd have to use decimal(38, 0) or some other larger value type rather than bigint.

So you deliberately set the value so that it would get too large for a bigint ... and that seems to be your error.

Not sure why you felt the need...

Theoretically @@IDENTITY value could be too large for a bigint.  Hard to imagine actually using enough values to go past 19 digits, but it's theoretically possible.

Btw, you should get @@IDENTITY...

Be sure you set and are using the correct variable.  In your original code, you use "@tblName" but you never set the value of that variable.  That's why I used...

You can't use a variable in a DROP TABLE statement in SQL.

Change it to:

SET @dropCommand = N'DROP TABLE dbo.' + @tableName

SQL data files could in theory gain from 64K cluster size, since SQL often writes data in 64K chunks.

SQL log files are different, so 32K should be just fine for...

If you issued an UPDATE against an empty table, the UPDATE counter would still increment.

Maybe that's what happened?

(1) more RAM (if you have only 300GB for SQL Server)

(2) do you (page) compress the data?

The @p_iType is declared in the called proc as a parameter.