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
WITHclause and the detailed clause with explicit index options? And, when theWITHclause 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.