SQL Server Index options SortInTempDB and DataCompression

[hmajerus]

I want to be able to use Fluent API to specify more index options. I have a particular interest in SORT_IN_TEMPDB and DATA_COMPRESSION.

Want something like:

indexBuilder.IsSortedInTempDb(true);

indexBuilder.HasDataCompression(DataCompression.Page);

This can be done by following the pattern set by the fill factor option - #20634.

SORT_IN_TEMPDB can be ON or OFF
DATA_COMPRESSION has the options NONE, ROW, and PAGE (link)

I could work this if determined it would be a good first issue.

[roji]

Sure, this qualifies as a good first issue - feel free to work on it.

HasDataCompresion should probably be named UseDataCompression.

[hmajerus]

Starting this now. Will take less than a month, but more than a few days depending on my availability.

[BrianMikinski]

Looking forward to tracking this issue and learning from your work. There are a couple more fluent API items i might attempt to implement if I get some spare time

[hmajerus]

When an index has multiple partitions, SQL Server let's you individually specify which data compression methods you want to apply to which partitions:
ALTER INDEX <index_name> ON <table_name>
REBUILD WITH(DATA_COMPRESSION = PAGE ON PARTITIONS(1, 2), DATA_COMPRESSION = ROW ON PARTITIONS(3, 4, 5))

When no partitions are specified, the data compression method is applied to all partitions:
ALTER INDEX <index_name> ON <table_name>
REBUILD WITH(DATA_COMPRESSION = PAGE)

My implementation currently only supports the latter right now. I did not add the partitions yet partially for simplicity, and partially because selfishly it doesn't really apply to me. I can update it now to consider it if desired.

One approach to support both would be to have the data compression annotation either be a DataCompressionType or a Lookup<DataCompressionType, int> depending on which of the above SQL outcomes the user desires. If this were to be added as a future iteration it would not be a breaking change on my current implementation.