Mariadb – Unable to create tables with ROW_FORMAT=COMPRESSED

You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.

The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your questions, is goes over the performance implications found when using data compression. My advice is going to be to test it. That is really the only way you will find out for sure if it will benefit or hurt your application/system.

A snippet from the whitepaper points out some data that does not benefit from data compression:

• Columns with numeric or fixed-length character data types where most values require all the bytes allocated for the specific data type

• Not much repeating data

• Repeating data with non-repeating prefixes

• Data stored out of the row

• FILESTREAM data

No, SQL Server does not do anything like this automatically. You can achieve some of what you're after with data compression, but its highest granularity is at the page level. So you'll get some good compression in the index pages if you have an index on only this column (or at least with this column as the leading key column), but assuming the clustered index is not on this column, you'll get much less benefit from it when the data and other index pages contain many variations of the string.

Data compression uses multiple algorithms, including dictionary, which would have the best impact if all of the values on a page are the same. Even at its best, though, this is not going to be extremely valuable on a large table - imagine you have a million pages and each page has two copies of one of those values. Sure, compression buys you some savings by storing the value only once instead of twice on each page (plus some negligible pointer overhead), but SQL Server still stores a million copies - one per page!

What I suggest, instead of storing the same dozen strings over and over again, is to create a lookup table with a TINYINT key, allowing you to only store each description string once, no matter how many times it's used. You can always retrieve the description at query time without ever having to store it with the data, and you can even create views to make this mostly transparent to queries, applications and users. This can also be a more attractive option if your workload is CPU-bound; while data compression saves you on storage and memory, there is some CPU overhead involved in compressing and decompressing each page.

For example:

CREATE TABLE dbo.TypeInfo
(
  TypeInfoID TINYINT PRIMARY KEY,
  Description VARCHAR(64) NOT NULL UNIQUE
);

INSERT dbo.TypeInfo(TypeInfoID, Description) VALUES
  (1,'very_long_descriptor_someone_came_up_with_a_long_time_ago'),
  (2,'this_desciptor_is_also_very_long'),
  (3,'nobody_knows_why_this_descriptor_is_so_long');
-- ... 9 other values ...

Now you need to add the TypeInfoID column to the original table:

ALTER TABLE dbo.OtherTable 
  ADD TypeInfoID TINYINT;

Then you can update the existing data as follows:

UPDATE o
  SET TypeInfoID = t.TypeInfoID
  FROM dbo.OtherTable AS o
  INNER JOIN dbo.TypeInfo AS t
  ON o.TypeInfo = t.Description;

(And process this in batches, if the table is large, and you want to minimize impact to the log and blocked operations.)

Then you can add a foreign key constraint:

ALTER TABLE dbo.OtherTable 
  ADD CONSTRAINT fk_TypeInfo 
  FOREIGN KEY (TypeInfoID) REFERENCES dbo.TypeInfo(TypeInfoID);

Once all of the data is verified to be correct, you can drop the column (first drop any constraints and indexes that reference it):

ALTER TABLE dbo.OtherTable 
  DROP COLUMN TypeInfo;

Then you can create a view that your queries can use to keep the semantics the same:

CREATE VIEW dbo.vOtherTable
AS
  SELECT /* o.columns */, TypeInfo = t.Description
    FROM dbo.OtherTable AS o
    INNER JOIN dbo.TypeInfo AS t
    ON o.TypeInfoID = t.TypeInfoID;

(Or in some scenarios you could even rename the table, and give the view the name the table used to have, making it completely transparent.)

And finally, if you don't want to allow NULLs in this column, and if you are on SQL Server 2012 or above, you can alter the column to NOT NULL without incurring updates and logging:

ALTER TABLE dbo.OtherTable 
  ALTER COLUMN TypeInfo TINYINT NOT NULL;

If you are on an earlier version, you might consider adding a CHECK CONSTRAINT instead:

ALTER TABLE dbo.OtherTable 
  WITH NOCHECK ADD CONSTRAINT ck_TypeInfoNotNULL
  CHECK (TypeInfoID IS NOT NULL);

But this may not be ideal, as an untrusted constraint can affect the ability of the optimizer to use it... in a lot of cases it is better to just take the one-time performance hit up front.

I think much of this is way beyond the scope of your original question, and you may not even consider any of this solution compelling, but if you do, you may want to do your own research and testing to ensure the least intrusive method of making this change. You can start with the following questions here and on Stack Overflow:

• Quickly change NULL column to NOT NULL
• Why does ALTER COLUMN to NOT NULL cause massive log file growth?
• Why does simple ALTER TABLE command take so long on table with full-text index?
• https://stackoverflow.com/questions/287954/how-do-you-add-a-not-null-column-to-a-large-table-in-sql-server