Recreating clustered columnstore index fails on sql server 2014

[alangsbo]

Hi All!

I´ve setup a project using an sql server 2014 db and ran in to an error when running models: "Incorrect syntax near the keyword 'IF'..."

This seems to occur in the macro sqlserver__create_clustered_columnstore_index because of the syntax DROP INDEX IF EXISTS {{relation_name}}.{{cci_name}} which will not work in sql server 2014 where the sys tables has to be queried instead.

I tried changing the code in my local fork to:

if exists (
        select * from
        sys.indexes where name = '{{cci_name}}'
        and object_id=object_id('{{relation_name}}')
    )
  drop index {{full_relation}}.{{cci_name}}

And it seems to work fine in my local dev environment

Br
Anders

[dataders]

@alangsbo so great to have people who can jump in and help provide a fix!

This adapter makes columnstore indices the default unless otherewise specified. This is also creates an issue for some tiers of Azure SQL where columnstore indices aren't supported.

You can either override this by adding {{ config( as_columnstore = false, ) }} to each model, or for the whole project by doing the following in your dbt_project.yml

models:
  your_project_name:
      +as_columnstore: false

We have this snippet in our README.md currently, but I think it could probably be made clearer, if you'd like to make a PR!

Will be materialized as columns store index by default (requires SQL Server 2017 as least). (For Azure SQL requires Service Tier greater than S2) To override: {{ config( as_columnstore = false, ) }}

[alangsbo]

sure! really excited about the growth of dbt-sqlserver and it´s nice to be a little part of it

About the readme, I think it´s perfectly clear, it is me who have to work on my RTFM skills :)

/A

[alangsbo]

Hi!

Actually reopening this after some consideration. This change will not affect whether clustered column store indexes are created or not, only the way they are created if they are set to be used

Will make a branch with the changes which we can test

/A