Assert verifies that index is the COLUMNSTORE index. Fail when index has not been found for a specified table, fails when index with a given name is not a column store index.

Note:
  - Supported in SQL Server framework versions.
  - 
CLUSTERED columnstore index supported in SQL Server 2014 Enterprise, Evaluation and Developer edition
  - 
NONCLUSTERED columnstore index supported in SQL Server 2012 Enterprise, Evaluation and Developer edition

Arguments

  • v_IndexName – index name, SYSNAME
  • v_ObjectName – table or view name, SYSNAME
  • v_IndexType - The Type of Index, VARCHAR(50) :
         - NONCLUSTERED_COLUMNSTORE 
         - CLUSTERED_COLUMNSTORE
         - for any column store index enter NULL, empty string or COLUMNSTORE
  • v_UserMessage – message to report when assertion fails, NVARCHAR(MAX)

Examples

 
SQL Server 

Below is assert that verifies filtered index created in the following statement:

CREATE TABLE DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE(
    ID    INT,
    ItemName  VARCHAR(500),
    NumberOfItems INT,
    CreateDate  DATETIME);
GO

INSERT INTO DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE
   (ID, ItemName, NumberOfItems, CreateDate)
   VALUES
     (1, 'ItemName_1', 10, '2011-01-01'),
     (2, 'ItemName_2', 10, '2012-01-01'),
     (3, 'ItemName_3', 10, '2013-01-01'),
     (4, 'ItemName_4', 10, '2014-01-01'),
     (5, 'ItemName_5', 10, '2015-01-01'),
     (6, 'ItemName_6', 10, '2016-01-01'),
     (7, 'ItemName_7', 10, '2017-01-01'),
     (8, 'ItemName_8', 10, '2018-01-01'),
     (9, 'ItemName_9', 10, '2019-01-01')
GO;

CREATE CLUSTERED COLUMNSTORE INDEX
   IXCS_DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE
      ON DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE;

EXEC DBTD_ASSERT_INDEX_COLUMNSTORE 
        'IXCS_DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE',
        'DBTD_TMP_COLUMNSTORE_CLUSTERED_TABLE', 
        'COLUMNSTORE',
        'Issues with index';

See Also