Unique index and filtered index of SQL Server index

Unique index

If the primary key is not set to a clustered index when it is created, it must be a unique nonclustered index. In fact, the unique index, the name Incredibles, is that it requires the value on that column to be unique. A unique index guarantees that the index key does not contain duplicate values, making each row in the table unique in some way. There is no obvious difference between creating a unique constraint and creating a unique index independent of the constraint. Data is validated in the same way, and the query optimizer does not differentiate whether a unique index is created by constraints or created manually. However, creating a UNIQUE constraint on a column makes the index target clearer. Unique Constraints and Check Constraints. "Xml:space=" preserve "id=" MT6 "> when creating a unique index, you can set an option that ignores duplicate keys. Yes and you attempt to create duplicate keys by adding data this affects multiple rows (with the INSERT statement), the RO W containing a duplicate is not added. "Xml:space=" preserve "id=" mt8 "> If this option is set to Yes, When you try to create a duplicate key by adding data that affects multiple rows (using the INSERT statement), the row that contains the duplicates is not added, and the entire INSERT operation fails and all the data are rolled back. " Xml:space= "preserve" id= "Mt9" > If this option is set to no, the entire insert operation will fail and all data will be rolled back.

Advantages of a unique index

• A multicolumn unique index guarantees that each combination of values in the index key is unique. For example, if you create a unique index for a combination of LastName,FirstName , and middlename columns, any two rows in the table will not have the same combination of these column values.
• As long as the data in each column is unique, you can create a unique clustered index and multiple unique nonclustered indexes for the same table.
• A unique index ensures the data integrity of the defined columns.
• The unique index provides additional information that helps the query optimizer generate more efficient execution plans.

Typical implementations

Unique indexes can be implemented in the following ways:

• PRIMARY KEY or UNIQUE constraint

When you create a PRIMARY KEY constraint, a unique clustered index is automatically created for one or more columns if the table's clustered index does not exist and a unique nonclustered index is not specified. The primary key column does not allow null values.

When you create a unique constraint, a unique nonclustered index is created by default to enforce a unique constraint. If there is no clustered index for the table, you can specify a unique clustered index.

• Unique Constraints and Check Constraints and Primary and Foreign Key Constraints.

Constraint-Independent indexes can define more than one unique nonclustered index for a table.

• Indexed views

To create an indexed view, you define a unique clustered index on one or more view columns. The view executes, and the result set is stored at the page level of the index in the same way that the table data is stored in the clustered index.

Limitations and limitations

• If duplicate key values exist in the data, you cannot create a unique index, a unique constraint, or a PRIMARY key constraint.
• A unique nonclustered index can include a non-key column that contains sex.

a unique index relies on a unique constraint, and deleting a unique index must remove a unique constraint. In addition , SQL Server establishes a unique index by default when it establishes a unique constraint.

Filtering a unique index allows you to use this when we need to allow multiple null values and not allow duplicates:

CREATE UNIQUE nonclustered INDEX xx on

Productdemo (< index column >)--Specify index column

where < indexed column >!=null)--Filter conditions

For a unique index created with the syntax above, when inserted, duplicates are detected only if the unique index column is not NULL. In other words, the index column of the above table allows multiple null values.

Filtered indexes (Filtered index)

A filtered index is an optimized nonclustered index, especially for queries that cover the selection of data from a well-defined set of data. The filtered index uses a filter verb to index some rows in the table. Well-designed filtered indexes can improve query performance, reduce index maintenance overhead, and reduce index storage overhead, compared to full-table indexes.

Filtered indexes have the following advantages over full-table indexes:

• Improved query performance and planning quality

A well-designed filtered index can improve query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

• Reduced index maintenance Overhead

The index is maintained only if the data manipulation language (DML) statement affects the data in the index. Filtered indexes reduce the cost of index maintenance when compared to a full table nonclustered index because it is smaller and is maintained only when the data in the index changes. The number of filtered indexes can be very large, especially if they contain data that is rarely changed. Similarly, if the filtered index contains only frequently modified data, the cost of updating statistics can be reduced if the index size is small.

• Reduced index storage Overhead

Creating a filtered index reduces the disk storage overhead of nonclustered indexes when it is not necessary to create a full table index. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing storage requirements.

Design Considerations

• You can create a filtered index on a subset of values when only a small number of related values in the column require a query. For example, you can create a filtered index for a non-null data row when the value in the column is mostly NULL and the query selects only from a non-null value. The resulting index is smaller and less expensive to maintain than a full table nonclustered index defined on the same key column.
• When a table contains heterogeneous data rows, you can create filtered indexes for one or more categories of data. This improves query performance for these data rows by narrowing the query to a specific area of the table. In addition, the resulting index is smaller and less expensive to maintain than a full-table nonclustered index.

Limitations and limitations

• You cannot create a filtered index on a view. However, the query optimizer can benefit from the filtered indexes defined by the tables referenced in the view. For queries that select data from a view, the query optimizer will consider using a filtered index for this query if the query results are correct.

Filtered indexes have the following advantages over indexed views:

• Reduced index maintenance overhead. For example, the query processor can update the filtered index with less CPU resources relative to the indexed view.
• Improve the quality of the plan. For example, during query compilation, the query optimizer considers using filtered indexes more than it would consider using an equivalent indexed view.
• The online index is regenerated. You can regenerate the filtered indexes when they are available to the query. Index view does not support Online index rebuilds
• Non-unique index. Filtered indexes can be non-unique, and indexed views must be unique.

• Filtered indexes are defined for a table, and only simple comparison operators are supported. If you need to reference more than one table or a filter expression with complex logic, you should create a view.

• If a filtered index expression is equivalent to a query predicate and the query does not return a column in the filtered index expression in the query results, the column in the filtered index expression does not need to be a key or include column in the filtered index definition.
• If a query predicate uses a column in a filtered index expression in a comparison that is not equivalent to a filtered index expression, the column should be a key or a containing column in the filtered index definition.
• If a column in a filtered index expression is in a query result set, the column should be a key or a containing column in the filtered index definition.
• The clustered index key for a table does not need to be a key or include column in the filtered index definition. The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.
• An error occurs when the conversion occurs to the left of the comparison operator if the comparison operator specified in the filtered index expression that filters the index result causes an implicit or explicit data conversion. The workaround is to write a filtered index expression that contains the data conversion operator (CAST or convert) on the right side of the comparison operator.

Unique index and filtered index of SQL Server index