adopt dbt-postgres's method of index creation

dbt-msft / dbt-sqlserver

dbt adapter for SQL Server and Azure SQL

MIT License

216 stars 101 forks source link

adopt dbt-postgres's method of index creation #163

Open dataders opened 3 years ago

dataders commented 3 years ago

copy the implementation introduced for dbt-postgres in https://github.com/dbt-labs/dbt/pull/3106/

davidclarance commented 3 years ago

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

dataders commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

@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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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