[SQL Server] [memo] selected index

Sql2008 adds a new member named "selected index" to the index type. In the above example, it is suggested that the selected index is an optimized non-replica SET index,

Specifically, this component is used to check the selected information from the defined subset.

Simply put, when a non-replica SET index is created, you can add a where clause with a single statement to improve the selection,

Actually improve the query efficiency, reduce the indexing and indexing costs.

 

The following is the situation where the student is admitted as follows:

When most of the values in the Data row are null and the query results will only be selected from non-null values (sparse data row ),

You can create an optional index for non-null data columns.

The index generated by the product is smaller than the non-dataset index of the Full-data table defined on the same index metadata row, and the cost of indexing is lower than that of indexing.

 

Cancel_reason:

Is null records: 190748. Is not null records: 9252.

 

Check the statement:

SELECT t1.BILL_NO,t1.CANCEL_REASON,t1.CANCEL_DATE FROM dbo.BILL_t t1 

WHERE t1.CANCEL_REASON IS NOT NULL

 

Create a non-replica SET index and selected index

-- Creating a non clustered index on CANCEL_REASON column

CREATE NONCLUSTERED INDEX nidx_CANCEL_REASON

ON BILL_t(CANCEL_REASON) 

INCLUDE(BILL_NO, CANCEL_DATE) --Including remaining columns in the index

GO

-- Creating a non clustered Filetered index on CANCEL_REASON column

CREATE NONCLUSTERED INDEX fnidx_CANCEL_REASON

ON BILL_t(CANCEL_REASON)

INCLUDE(BILL_NO, CANCEL_DATE) --Including remaining columns in the index

WHERE CANCEL_REASON IS NOT NULL --filter criteria for the index

GO

 

View index related information

SELECT t2.index_id, t2.name, t2.type_desc, t1.reserved_page_count, 

t1.used_page_count,t1.row_count,t2.filter_definition 

FROM sys.dm_db_partition_stats t1

INNER JOIN sys.indexes t2 ON t1.object_id = t2.object_id AND t1.index_id = t2.index_id

WHERE t1.object_id = OBJECT_ID('BILL_t')

It can be seen that the storage cost of the selected index is much lower than that of the general non-dataset index, which is quite time-saving for indexing statistics.

 

Use General non-datasets for billing, Io, and experience

The cost of using a general non-dataset index for the whole-body computing is 0.0845703.

 

 

Use the optional index to calculate, Io, and experience

First, clear the related buffer.

 

 

The cost of using a general non-dataset index for the whole-body computing is 0.0164222.

 

Result comparison

 

The cost of the entire system is about five times different.