Support for SQL-Server ColumnStore?

[CaselIT]

Discussed in #7333

^{Originally posted by MarcusJellinghaus November 16, 2021}
Hello,

I have this code to create a new table based on the structure of an existing table.

source_engine = driver_connection.create_sqlserver_engine(source_server, source_database)
engine_new = driver_connection.create_sqlserver_engine(new_server, new_database)

import sqlalchemy

smeta = sqlalchemy.MetaData(bind=source_engine)
table = sqlalchemy.Table(source_table_name, smeta, schema=source_schema_name, autoload=True)
table.metadata.create_all(engine_new)

I'm using the latest SQL Server (2019) and the table is based on columnstore,

The creation of the table does not work well. The creation of the columnstore index fails.

Is this expected to be supported? Did I overlook something? Or shall I prepare python and SQL scripts to report this as an issue?

[zzzeek]

do we have any information at all what is needed here, how to do it, how to test it?

[MarcusJellinghaus]

Hello,
Microsoft SQL-Server columnstore indices are especially useful for analytical databases (with loads of reading, few updates or deletes).

Creation of SQL-Server Columnstore indices is documented
More information is also available in the related discussion.

[CaselIT]

do we have any information at all what is needed here, how to do it, how to test it?

I would guess a new mssql flag on an index that renders it with the required keyword.
Not sure if there are further changes required for the reflection here

[CaselIT]

Ok, tried. new flag + change to compile index + reflection change

[sqla-tester]

Federico Caselli has proposed a fix for this issue in the main branch:

Add mssql_columnstore to index options. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4738