Implement bitmap indexes & index compression for MS SQL

[Anthony Valencia]

currently, arguments such as oracle_bitmap & oracle_compress clearly specify the database type. while this clearly indicates the supported databases, it leads me to believe that every database supporting bitmap indexes & index compression will have its own set of named arguments, as opposed to having the named arguments, bitmap & compress, while listing supported databases for those arguments. perhaps implement a warning if it is turned on for an unsupporting database? just some thoughts.

[Mike Bayer]

per http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems we see BITMAP is on DB2 and Oracle; it says "yes" for Postgresql, but this isn't really accurate since it's implemented in some other way and there is no BITMAP keyword at http://www.postgresql.org/docs/9.1/static/sql-createindex.html. For SQL Server, Wikipedia claims there is no BITMAP support nor do I see anything at https://msdn.microsoft.com/en-us/library/ms188783.aspx. So this seems so far like a keyword that is only appropriate for Oracle and DB2 so far (DB2 is a 3rd party dialect).

for COMPRESS, the syntaxes for Oracle and SQL Server look completely different, Oracle uses "COMPRESS 1" whereas SQL Server has "DATA_COMPRESSION = { NONE | ROW | PAGE} ", so the specification of these options is not agnostic on those two backends; one would want to be able to specify the boolean for Oracle while specifying the "ROW" or "PAGE" flag for SQL Server.

Additionally, with very specific index performance options like these, it is often the case that one would want to implement the options differently on different backends.

so far these two options look much too narrow and vendor-specific to be good candidates for a generic option.

if the proposal is an option like "compression = ('oracle', 'sqlserver')", that doesn't provide any space for the special values that are specific to Oracle vs. SQL Server and I don't see how that is meaningfully any better or clearer than just having "oracle_compression=1, mssql_compression='ROW'".

[Mike Bayer]

additionally, with warnings, that's not what people want here. If someone said "bitmap = ('oracle', 'postgresql')" and it was ignored with just a warning, it's not like someone would want the "bitmap" feature to suddenly be used on a target backend the moment Postgresql and SQLAlchemy started supporting it, rather than being able to add it to their application only when the feature is available so that it can be tested. Database features like these aren't typically just thrown at a backend without careful testing to ensure that the specific planner behavior of that vendor backend makes it a worthwhile flag.

[Mike Bayer]

no further activity here, closing.