Suggested Tables
You can use suggested_tables.sql script or as an alternative a stored procedure cstore_GetSuggestedTables for listing tables which potentially can be interesting for implementing Columnstore Indexes.
The script scans through all available tables in the current database and analyses them for being compatible with possible types of Columnstore Indexes (Clustered & Nonclustered) and returns information on them.
- @showTSQLDropCommands parameter is in alpha version and not pretending to be complete any time soon. This output is provided as a basic help & guide convertion to Columnstore Indexes.
- CLR support is not included or tested
- Output [Min RowGroups] is not taking present partitions into calculations yet :)
@minRowsToConsider bigint = 500000 Defines the minimum number of rows for a table to be considered for the suggestion inclusion. The current default value is 500.000 rows.
@minSizeToConsiderInGB Decimal(16,3) = 0.00 Defines the minimum size in GB for a table to be considered for the suggestion inclusion. The current default value is 0.00, which means that no table shall be filtered by default.
@ schemaName nvarchar(256) = NULL Allows to show data filtered down to the specified schema.
@tableNamePattern nvarchar(256) = NULL
Allows to show data filtered down to the specified table name pattern.
@considerColumnsOver8K bit = 1 Include in the results tables, which columns sum extends over 8000 bytes (and thus not supported in Columnstore Indexes in SQL Server 2014)
@showReadyTablesOnly bit = 0 Shows only those Rowstore tables that can already get Columnstore Index without any additional work.
@showUnsupportedColumnsDetails bit = 0 Shows a list of all Unsupported from the listed tables.
@showTSQLCommandsBeta bit = 0 Shows a list with Commands for dropping the objects that prevent Columnstore Index creation.
@columnstoreIndexTypeForTSQL varchar(20) = 'Clustered' Allows to define the type of Columnstore Index to be created eith possible values of 'Clustered' and 'Nonclustered'