Detailed SQL Server focused filtering index _mssql

Objective

In this section, we will continue to talk about indexing knowledge, in front of our clustered index, nonclustered index and overlay index, in which there is also a filtered index, through the index filter we can improve query performance, short content, in-depth understanding.

Filter indexes to create nonclustered indexes on query criteria (1)

The filtering index is a new feature of SQL 2008 that is applied to some of the rows in the table, so using a filtered index improves the query and reduces the cost of index maintenance and index storage relative to the full table scan. The index is filtered when we apply the Where condition on the index. That is, the following format is satisfied:

CREATE nonclustered INDEX <index name> on
<table> (<columns>)
WHERE <criteria>;
Go

Now let's look at a simple query

Use AdventureWorks2012
go
SELECT salesorderdetailid, UnitPrice from
sales.salesorderdetail
WHERE UnitPrice > Go

None of the indexes are established in the above column, except, of course, the clustered index created by default Salesorderdetailid, in which case we can guess that the query plan it executes must be a clustered index scan created by the primary key, as follows

As we have said at this time, the index is not created on the query criteria, so it is inevitable that the primary key creates a clustered index, and then we first create a nonclustered index on the UnitPrice column to improve query performance.

CREATE nonclustered INDEX idx_salesorderdetail_unitprice on
sales.salesorderdetail (UnitPrice)

Now we're going to compare the query overhead.

Use AdventureWorks2012
go
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT Salesorderdetailid, UnitPrice from
AdventureWorks2012.Sales.SalesOrderDetail with ([pk_salesorderdetail_salesorderid_ Salesorderdetailid])
WHERE unitprice > Go
SELECT salesorderdetailid, UnitPrice from
Sales.SalesOrderDetail with INDEX ([Idx_salesorderdetail_unitprice])
WHERE UnitPrice > 2000

After a nonclustered index is established on the query condition, the query overhead is significantly elevated, up to more than 90%, because the nonclustered index also references the clustered index created by the primary key, so this time does not cause either the bookmark lookup or the key lookup lookup. Next we're going to add a nonclustered index with a condition that filters the index

CREATE nonclustered INDEX idxwhere_salesorderdetail_unitprice on
sales.salesorderdetail (UnitPrice)
WHERE UnitPrice > 1000

Now let's take a look at the difference between the performance cost of nonclustered indexes after the filtered index has been created:

Use AdventureWorks2012
go
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT Salesorderdetailid, UnitPrice from
AdventureWorks2012.Sales.SalesOrderDetail with ([Idx_salesorderdetail_unitprice])
WHERE UnitPrice >
SELECT salesorderdetailid, UnitPrice from
Sales.SalesOrderDetail with (INDEX ( Idxwhere_salesorderdetail_unitprice])
WHERE UnitPrice > 2000

At this point we know that the nonclustered filtered index we created is nearly half as close to our query as the traditional nonclustered index created.

Unique Filter Index

Unique filtering indexes must be unique and not empty (allow only one null presence) for all columns, so you will need to exclude null values when creating a unique filtered index, such as the following:

CREATE UNIQUE nonclustered INDEX uq_fix_customers_email on
Customers (email)
WHERE email isn't NULL go

Filter index combination include

When we add an extra column, the clustered index created by the default primary key is used. This will take a clustered index scan, and then we create a filter index on the query criteria, and when we force this filter index, the addition of additional columns will result in the need to return to the base table to fetch the data, so it creates a key Lookup lookup, as follows:

Use AdventureWorks2012
go
SELECT salesorderdetailid, UnitPrice, Unitpricediscount from
Sales.SalesOrderDetail
WHERE UnitPrice > Go

At this point we need to include the extra columns with include.

CREATE nonclustered INDEX [Idx_salesorderdetail_unitprice] on Sales.SalesOrderDetail (UnitPrice) INCLUDE ( Unitpricediscount)

We'll create a filtered index and include extra columns

CREATE nonclustered INDEX [Idxwhere_salesorderdetail_unitprice] on Sales.SalesOrderDetail (UnitPrice) INCLUDE ( Unitpricediscount)
WHERE UnitPrice > 2000

Then we'll do the comparison. Adding a filtered index and not adding a filtered index also includes performance query variances for additional columns.

SELECT Salesorderdetailid, UnitPrice, unitpricediscount from
AdventureWorks2012.Sales.SalesOrderDetail with ( INDEX ([Idx_salesorderdetail_unitprice]))
WHERE unitprice > 
SELECT salesorderdetailid, UnitPrice, Unitpricediscount from
sales.salesorderdetail with ([Idxwhere_salesorderdetail_unitprice])
WHERE UnitPrice > 2000

Performance with include to include additional column performance has also improved somewhat.

Filter index, create nonclustered index on primary key (2)

In the first column, we can create a nonclustered index directly on the query column because its type is numeric, what if the query condition is a character type? First choice now we create a test table

Use TSQL2012
go
CREATE TABLE dbo. TestData 
(
  RowID    integer IDENTITY not null, 
  somevalue  VARCHAR (max) is not NULL,   
  StartDate  date not NULL,
  CONSTRAINT pk_data_rowid
    PRIMARY KEY CLUSTERED (RowID)
);

Add 100,000 test data

Use TSQL2012
go
INSERT dbo. TestData with (Tablockx)
  (somevalue, StartDate)
SELECT
  CAST (n.n as VARCHAR (max)) + ' Jeffckywang ',
  DATEADD (Day, (n.n-1)%, ' 20140101 ') from
dbo. Nums as N
WHERE 
  n.n >= 1 and 
  N.N < 100001;

If we need to get somevalue = ' Jeffckywang ' in the table testdata, we want to create a nonclustered index on somevalue and then filter it, as follows

Use TSQL2012
go
CREATE nonclustered INDEX idx_noncls_somevalue on
dbo. TestData (somevalue)
WHERE somevalue = ' Jeffckywang '

Update

SQL Server has a limit on the size of the index to create, the maximum is 900 bytes, the above directly written varchar (max), so there will be errors, remember, remember.

At this point we create a nonclustered index on the primary key, we create a filtered index on the primary key rowid and somevalue = ' Jeffckywang ', and then return the data as follows:

CREATE nonclustered INDEX idxwhere_noncls_somevalue on
dbo. TestData (RowID)
WHERE somevalue = ' Jeffckywang '

Here we compare the results of the query plan before and after setting up the filtering index:

Use TSQL2012
go
SELECT RowID, Somevalue, startdate from 
dbo. TestData with (INDEX ([Idx_pk_rowid]))
WHERE somevalue = ' Jeffckywang '
SELECT rowid, somevalue, StartDate 
FROM dbo. TestData with (INDEX ([Idxwhere_noncls_somevalue]))
WHERE somevalue = ' Jeffckywang '

Then, with the previous learning, remove key Lookup and include the filtered index created.

CREATE nonclustered INDEX [Idxwhere_noncls_somevalue] on dbo. TestData (RowID) INCLUDE (somevalue,startdate) 
WHERE somevalue = ' Jeffckywang '

From here, whether you create a filter index on a query condition or a filter index on a primary key, we can improve query performance by combining what we learned before.

We've been talking from the beginning. To create a filtered index, what are the criteria for creating the advantages of filtering indexes?

(1) can only be created through a nonclustered index.

(2) If you create a filtered index on a view, this view must be a persisted view.

(3) You cannot create a filtered index on a full-text index.

Advantages of filtering indexes

(1) Reduce the cost of index maintenance: For the increase, delete, change and other operations do not need to cost less expensive, because a filter index to rebuild does not take too much time.

(2) Reduce storage cost: The storage footprint of the filtering index is very small.

(3) More accurate statistics: By creating a filtered index on the Where condition, it is more accurate than the whole table statistic.

(4) Optimization of query performance: Through the query plan can see its efficiency.

So far, it has been stated that the advantages and benefits of filtering the index, has been the blessing of the sky, in fact its shortcomings are obvious.

Filtering Index Disadvantage

The biggest disadvantage is the restriction of query conditions. Its query criteria are limited to

<filter_predicate>:: =  
  <conjunct> [and <conjunct>] 
<conjunct>:: = 
  <disjunct > | <comparison>  
<disjunct>:: = 
    column_name in (constant,... N)

Filter conditions are limited to and, |, in. The comparison condition is limited to {is | is not | = | <> |!= | > | >= |!> | < | <= |!<}, so use like no

CREATE nonclustered INDEX [Idxwhere_noncls_somevalue] on dbo. TestData (RowID) INCLUDE (somevalue,startdate) 
WHERE somevalue like ' jeffckywang% '

The following can be

Use AdventureWorks2012
go
CREATE nonclustered INDEX idx_salesorderdetail_modifieddate
on Sales.SalesOrderDetail (modifieddate)
WHERE modifieddate >= ' 2008-01-01 ' and ModifiedDate ' 2008-01-07 ' Go

as follows but not

CREATE nonclustered INDEX idx_salesorderdetail_modifieddate on
sales.salesorderdetail (modifieddate)
WHERE ModifiedDate = GETDATE () go

Effects of variables on filtering indexes

Above we create a filter index that is directly defined on the query criteria, as follows:

CREATE nonclustered INDEX idxwhere_salesorderdetail_unitprice on
sales.salesorderdetail (UnitPrice)
WHERE UnitPrice > 1000

If you define a variable, what happens when you use a variable to compare it? First we create a filtered index

CREATE nonclustered INDEX Idx_salesorderdetail_productid on 
sales.salesorderdetail (ProductID)
WHERE ProductID = 870

Using variables to compare with query conditions, force filtering indexes (by default, go to clustered indexes)

Use AdventureWorks2012
go
DECLARE @ProductID INT 
SET @ProductID = 870 
SELECT ProductID 
from Sales.SalesOrderDetail with (INDEX ([Idx_salesorderdetail_productid]))
WHERE ProductID = @ProductID

To view the query execution plan results there was an error, at which point we need to add option to recompile, as follows:

Use AdventureWorks2012
go
DECLARE @ProductID INT 
SET @ProductID = 870 
SELECT ProductID 
from Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION (RECOMPILE)

The above use variables to query the last by option recompile in SQL Server 2012 test so that, as other versions are unknown, reference "The Pains of filtered Indexes".

Summarize

In this section, we learn to improve query performance by filtering indexes, as well as their different scenarios and their use advantages and obvious drawbacks. Short content, in-depth understanding, we see the next section, Good night.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!