SQL Server Index and table architecture (including column indexes)

Original: SQL Server index and table architecture (including column index)

Include column index

Overview

The include column index is also a nonclustered index, and the index structure is the same as the clustered index structure, where the non-key column containing the column index is stored only on the leaf node, the columns that contain the column index are the key and nonkey columns, the so-called nonkey columns are the columns included in the include, and at least one key column is required. And the key and nonkey columns do not allow duplicates, the non-key column allows a maximum of 1023 columns (that is, the table's maximum column-1), because the index key columns (excluding non-keys) must adhere to the existing index size limit (the maximum number of key columns is 16, the total index key size is 900 bytes) The Inclusion column index is introduced

Body

• Create a include column index

----Create a tableCREATE TABLE [dbo].[Customers](    [CustID] [int] IDENTITY(1,1) not NULL,    [CompanyName] [nvarchar]( +) not NULL,    [ContactName] [nvarchar]( -) not NULL,    [ContactTitle] [nvarchar]( -) not NULL, CONSTRAINT [pk_customers] PRIMARY KEY CLUSTERED (    [CustID] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]----Create a include column indexCREATE nonclustered INDEX [ix1_customers]  on [dbo].[Customers] (    [CompanyName] ASC) INCLUDE ([ContactName])
 with(Statistics_norecompute= OFF, sort_in_tempdb= OFF, Ignore_dup_key= OFF, drop_existing= OFF, ONLINE= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY]GO

The key column here is:CompanyName
Non-key columns are: ContactName

Non-key columns have the following advantages:

• They can be data types that are not allowed as index key columns.
  
  
• The database engine does not consider them when calculating the number of index key columns or index key sizes.
  
  

Indexes with inclusive nonkey columns can significantly improve query performance when all columns in the query are included as key or nonkey columns in the index. This enables performance gains because the query optimizer can find all column values in the index, and does not access table or clustered index data, thereby reducing disk I/O operations. (When an index contains all the columns referenced by a query, it is often referred to as an "overwrite query.") ）

• Create an overwrite query

Overriding a query is when you create an indexed column that contains all the columns that the query references

1. Query columns are set as key columns

when our select query is like thisSELECT        [CompanyName]      ,[ContactName]      ,[ContactTitle]   from [CHENMH].[dbo].[Customers]  whereCompanyName='good boy.'    ---At this point we choose to include the index column in the index columnCREATE nonclustered INDEX [ix2_customers]  on [dbo].[Customers] (             [CompanyName] ASC      ,[ContactName] ASC      ,[ContactTitle] ASC) with(Statistics_norecompute= OFF, sort_in_tempdb= OFF, Ignore_dup_key= OFF, drop_existing= OFF, ONLINE= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY]GO

A warning will pop up: Warning! The maximum key length is 900 bytes. The maximum length of index ' ix2_customers ' is 940 bytes. For some large value combinations, the Insert/update operation will fail.
Since all three fields are nvarchar field types, each character needs 2 bytes, (40+30+400) *2=940 bytes, greater than 900 bytes, we can add a[ContactName], [ContactTitle] contained in non-key columns

2. Set the big data type to a non-key column

CREATE nonclustered INDEX [ix3_customers]  on [dbo].[Customers] (             [CompanyName] ASC) INCLUDE ([ContactName]        ,[ContactTitle]) with(Statistics_norecompute= OFF, sort_in_tempdb= OFF, Ignore_dup_key= OFF, drop_existing= OFF, ONLINE= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY]GO

At this point, the index key size of the character is only 40*2=80 bytes, and the index is also an overlay index, the index column contains the columns used by the query, when we query the data directly in the index page to find data, do not need to access the data page, reduce disk IO, improve performance

index criteria with included columns

When designing a nonclustered index with a containing column, consider the following guidelines:

• • Defines a nonkey column in the INCLUDE clause of the CREATE INDEX statement.
    
  • You can define non-key columns only for nonclustered indexes on a table or indexed view.
    
  • All data types are allowed except text,ntext , and image .
    
  • Deterministic computed columns that are precise or imprecise can be include columns. For more information, see Create an index on a computed column.
    
  • As with key columns, computed columns derived from the image,ntext , and text data types can be used as non-key (inclusive) columns as long as the computed column data type is allowed as a non-key index column.
    
  • You cannot specify a column name in both the INCLUDE list and the key column list.
    
  • Column names in the INCLUDE list cannot be duplicated.

Column Size Guidelines

• • At least one key column must be defined. The maximum number of non-key columns is 1023 columns. That is, the maximum number of table columns minus 1.
    
  • Index key columns (excluding non-keys) must adhere to the existing index size limit (the maximum number of key columns is 16 and the total index key size is 900 bytes).
    
  • The total size of all nonkey columns is limited only by the size of the column specified in the INCLUDE clause, for example, thevarchar (max) column is limited to 2 GB.

Column Modification Guidelines

When you modify a table column that is already defined as a containing column, you are constrained by the following:

• • Non-key columns cannot be removed from the table unless the index is deleted first.
  • You cannot make other changes to non-key columns except for the following changes:
    
    
    • Changes the nullability of a column from NOT NULL to NULL.
    • Increase the length of the varchar,nvarchar , or varbinary columns.

Precautions

• The size of the key column is as small as possible, with improved efficiency
• The column that will be used for search and lookup is the key column, and the key column should try not to include unnecessary columns. (for example, the above-established coverage query column, although companyname+ContactName added up as a key column will not exceed 900 bytes, but the size of the key is larger, reducing the query efficiency)

• Avoid adding unnecessary columns. Adding too many indexed columns (key columns or nonkey columns) has the following effects on performance:
  • Fewer index rows can be accommodated on a page. This increases the I/O and reduces cache efficiency.
    
  • More disk space is required to store the index. In particular, adding varchar (max),nvarchar (max),varbinary ( max), or XML data types as Nonkey index columns can significantly increase disk space requirements. This is because the column values are copied to the index leaf level. Therefore, they reside both in the index and in the base table.
    
  • Index maintenance may increase the time that is required to perform modifications, inserts, updates, or deletions on the underlying table or indexed view

Summarize

    If you think the article is helpful to you, Active activities your finger trouble to give a referral, which is also an encouragement to me, here to express my thanks.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server Index and table architecture (including column indexes)