You've done well (although I only see 4 replies of yours on this forum and with that I'll say "Welcome Aboard") and my hat is off to you for helping...

THANKS!

Question: can there be any reason that an error will be thrown while attempting to compress the table? Like what should be 'wrong' with table to cause the compression...

Thank you for your feedback, appreciate your valuable time, skills and know ledges. Do you agree if we have more than 5 millions rows in source table, we...

Here's the test data using the "old fashioned" way that works in all versions of SQL Server since at least 6.5.

--===== Create the test table from what...

@tpd1989 ,

This is the KEY question on this thread.  Please confirm that you are actually using SQL 7 or 2000 or...

@bill,

Neither CROSS APPLY nor the multiline version of VALUES is available in 7/2000.

THANKS!

Question: can there be any reason that an error will be thrown while attempting to compress the table? Like what should be 'wrong' with table to cause the compression...

We might even be able to save those 4 extra characters - depending on the source data.  If we know for sure that we have a minimum length of...

Sounds good.  Looking forward to it.

It's also great for the archival of data.  Please see "Example B" at the following link.

https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver15

It could also be an easy way to ZIP such data for data transmission...

This is a nice narrow table.  You can do a lot of what you ask for auto-magically in 2017 using System-Versioned Temporal Tables.  It will not only record the history...

For my part I really appreciate the deep analysis into components.  It's vital to make progress in a good way so I think it's essential research.  It NEVER takes...

• This reply was modified 6 years ago by Jeff Moden.

Just one point - not sure it makes a difference in performance but you can avoid this:

@BitBucket = RIGHT('00000000000000'+CONVERT(VARCHAR(14),Col01),14)

By using this:

@BitBucket = RIGHT(CONCAT('00000000000000',...

IF the pattern is consistent, this is easy.

--===== Create the test data.  This is not a part of the solution.
 CREATE TABLE #Temp
  ...

>> THIS coming from the person that says he likes the MySQL "standard" of using YYYY-00-00 as the notation for whole years and YYYY-MM-00 for whole months. What a...