We need to support filtered index for extract, script, visual editing and the ER modeler.
create unique nonclustered index idx1 on [schema].[table] ( [col1] ) where [col1] is not null
the extraction & scripting for filtered indexes was completed 6/11/2010.
AFindex has the following two variables that apply to filtered indexes:
boolean _hasFilter default is false
For the GUI changes we could add a section in the options tab for WHERE: <statement>
WHERE <filter_predicate>
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20040101' AND EndDate <= '20040630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL
Filtered indexes do not apply to XML indexes and full-text indexes. For UNIQUE indexes, only the selected rows must have unique index values. Filtered indexes do not allow the IGNORE_DUP_KEY option.
removed _hasFilter
removed _hasFilter
Added "Filter Predicate" option for SQL Server 2008 and later.
Decided against adding a separate tab, as it would complicate the UI code for me. Instead, it's added to Options as a regular option, with a popup dialog, which works the same in ER modeler and visual editing.
Added "Filter Predicate" option for SQL Server 2008 and later.
Decided against adding a separate tab, as it would complicate the UI code for me. Instead, it's added to Options as a regular option, with a popup dialog, which works the same in ER modeler and visual editing.
Issue #6163 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
No fixed build |
No time estimate |
the extraction & scripting for filtered indexes was completed 6/11/2010.
AFindex has the following two variables that apply to filtered indexes:
boolean _hasFilter default is false
For the GUI changes we could add a section in the options tab for WHERE: <statement>
WHERE <filter_predicate>
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20040101' AND EndDate <= '20040630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL
Filtered indexes do not apply to XML indexes and full-text indexes. For UNIQUE indexes, only the selected rows must have unique index values. Filtered indexes do not allow the IGNORE_DUP_KEY option.