0

The difference between simple and detailed WITH clauses:

In YouTube tutorial SQL Server Tutorial 20: Table, Index and Row Compression, the example at time 5:43 contains a simple WITH clause of the index options, only WITH (DATA_COMPRESSION = ROW).

USE myDatabase;
CREATE TABLE dbo.Ch7RowCompression
(
ID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
BirthDate datetime
)
WITH (DATA_COMPRESSION = ROW);

However, with GUI Management Studio, the Storage > Manage Compression... wizard generates commands with a more detailed WITH clause, explicitly giving values on the index options. For example, to compress an index, it gets the below sample command:

ALTER INDEX [SALES_ORDER_CREATED_AT] ON [schema].[sales_order] 
REBUILD PARTITION = ALL WITH (
  PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
, DATA_COMPRESSION = PAGE
)
Another observation about GUI Management Studio's behavior:

The GUI Management Studio generates wrong commands for un-doing an index compression.

We have already enabled Script Data Compression Options to True in the context of Management Studio > Toolbar > Tools > Options... > SQL Server Object Explorer > Scripting > Object scripting options. The system under test has Management Studio 2016 in version 13.0.16106.4 and SQL Server 2016. Please let me know if you need more details.

We suspect the system behavior is wrong when undoing compression on an index: For example, by setting the compression from "Page" to "None", the wizard gets the below command, missing DATA_COMPRESSION = NONE. And we have confirmed in the test that the command does not undo the compression.

ALTER INDEX [SALES_ORDER_CREATED_AT] ON [schema].[sales_order] 
REBUILD PARTITION = ALL WITH (
  PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
)

The wizard works OK for undoing compression on a table, though, and the generated command includes DATA_COMPRESSION = NONE as expected.

Our Questions:
  • How do we get a SQL Server command for doing and un-doing compression? By GUI wizard, by handwriting, or better ways?

  • What is the difference between the simple WITH clause and the detailed clause with explicit index options? And, when the WITH clause does not contain an option, what will be the default value?

  • Just double-checking, is everyone else seeing the same symptom in GUI Management Studio trying to undo an index compression? Not sure if it's a bug or if we missed something.

We appreciate any hints and suggestions.

1 Answer 1

1

The developers who created SSMS (SQL Server Management Studio) are not necessarily the same developers who worked on the SQL Server product itself. So there are definitely some discrepancies between what code SSMS auto-generates and what the ideal code would be for certain scenarios.

How do we get a SQL Server command for doing and un-doing compression? By GUI wizard, by handwriting, or better ways?

Your best bet is to always write the script yourself instead of relying on SSMS. It'll give you the most flexibility and reliability, besides causing you to learn and understand what the code actually does.

What is the difference between the simple WITH clause and the detailed clause with explicit index options?

The detailed WITH clause generated by SSMS is just including every index rebuild option available with its current value for that index, regardless if that's the default value or not, and regardless of that option relates to compression. SSMS doesn't track what values are the default values for each of those options, and therefore to be safe, it just generates every option with its current value, to ensure an option isn't accidentally changed by being omitted.

And, when the WITH clause does not contain an option, what will be the default value?

That depends on the specific option and its normal default value, which would be specified in Microsoft's documentation on SQL Server.

Just double-checking, is everyone else seeing the same symptom in GUI Management Studio trying to undo an index compression? Not sure if it's a bug or if we missed something.

Yes. Bug?...maybe or arguably just by design (really lack of design).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.