Assert verifies that FILTERED index exists with given name, expected filter criteria and for a specified table. Fail when index has not been found for a specified table, fails when filtering criteria do not match, or when index is is any other type but filtered.

Note:
- Supported in SQL Server framework versions.
When v_IndexFilterDefinition value is NULL or empty, assert will not check the definition, but would still verifies if index is filtered

Arguments

  • v_IndexName – index name, SYSNAME
  • v_ObjectName – table or view name, SYSNAME
  • v_IndexFilterDefinition - expected filter dedinition,  NVARCHAR(MAX)
  • 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 NONCLUSTERED INDEX
    IX_ProductTable_Filtered_Note_IsNotNULL
       ON ProductTable (Note)
       WHERE Note IS NOT NULL;

EXEC DBTD_ASSERT_INDEX_FILTERED 'IX_ProductTable_Filtered_Note_IsNotNULL', 'ProductTable', '([Note] IS NOT NULL)', 'Issues with filtered index';

See Also