dbt-sqlserver icon indicating copy to clipboard operation
dbt-sqlserver copied to clipboard

Published 20 hours ago • verified publisher icon dbt-msft

adopt dbt-postgres's method of index creation

Open dataders opened this issue 3 years ago • 7 comments

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

dataders avatar Oct 05 '21 17:10 dataders

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

davidclarance avatar Oct 08 '21 05:10 davidclarance

@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.

dataders avatar Oct 14 '21 00:10 dataders

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.

semcha avatar Oct 25 '21 11:10 semcha

@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?

dataders avatar Oct 25 '21 14:10 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

semcha avatar Nov 29 '21 21:11 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

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

dataders avatar Nov 29 '21 23:11 dataders

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

semcha avatar Dec 18 '21 20:12 semcha