adopt dbt-postgres's method of index creation

[dataders]

copy the implementation introduced for dbt-postgres in dbt-labs/dbt-core#3106

[davidclarance]

@swanderz this implementation is neat! could I work on this issue or is someone already working on it?

[dataders]

@davidclarance all you! I did a version of this for a soon to be open-source adapter, so reach out if you have any questions.

[semcha]

Looks intresting : )
But SQL Server have:

• clustered/non-clustered indexes
• limit to clustered index (one per table)
• row-store (b-tree)/columnstore indexes
• non-clustered row-store index may have included columns: INCLUDE (col1, col2)
• data compression: ROW and PAGE compression for row-store and COLUMNSTORE_ARCHIVE for columnstore
• COMPRESSION_DELAY option for columnstore
• online creation option, which works only in Enterprise Edition: ONLINE = ON
• partitioning (partition schema name and partition column)
• filter for non-clustered indexes (row-store and columnstore): WHERE col1 = 'abc'
• and, of course, uniqueness(UNIQUE) for row-store indexes (clustered and non-clustered)

Thus, it requires logic more complicated than the PostgreSQL implementation.

[dataders]

@semcha great point, especially since Synapse has it's own set of indexes. However, there's nothing I think that couldn't be described in a key-value dictionary, right?

[semcha]

Hi @swanderz!
Do I understand correctly that we need to implement this macro for SQL Server and name it sqlserver__get_create_index_sql?
https://github.com/dbt-labs/dbt-core/pull/3106/files#diff-49fbdf0ee41b3d9ec139a881dfed1e9196c47ba014f946421f636bbf57703a6c

[dataders]

Hi @swanderz! Do I understand correctly that we need to implement this macro for SQL Server and name it sqlserver__get_create_index_sql? https://github.com/dbt-labs/dbt-core/pull/3106/files#diff-49fbdf0ee41b3d9ec139a881dfed1e9196c47ba014f946421f636bbf57703a6c

yep! additionally we're need a SQLServerIndexConfig for the index definition json. For another example check out dbt-firebolt's FireboltIndexConfig

[semcha]

Working on that issue here: https://github.com/semcha/dbt-sqlserver/commits/new-indexes