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