Can we create cover index during cluster index creation

Question

i have no idea about cover index. just see this video https://www.youtube.com/watch?v=yefP63XqlpY

suppose my table has ID column which is identity type. when i will create cluster index on this column then can i create non cover index ?
can include other field name ?

or only cover index can be created with non-cluster index ? do not know.

if i create cover index then does it create any negative impact on table if many time data is inserted into that table ?

please advise. tell me best index creation approach. looking for suggestion. thanks

Answer 1

Hi @Sudip Bhatt ，

Covering index is a concept introduced in SQLServer2005. It could only be built on the basis of non-clustered index.

CREATE NONCLUSTERED INDEX {index_name}  
ON {table_name}(column_name...)   
INCLUDE(column_name...)   

Generally, the index page of non-clustered index does not contain real data, and only stores pointers to data rows in data pages.

The covering index is to store data on the index page, so that when searching for the corresponding data, as long as the index page is found, the data can be accessed, and there is no need to query the data page, so this index is data "covered".

The clustered index is actually a covering index. In most cases, you can intuitively distinguish whether the current clustered index of the data table is useful, because the clustered index controls the order of the data rows according to the key value field.

By including frequently queried columns in nonclustered indexes, we can dramatically improve query performance by reducing I/O costs. Since the data pages for an nonclustered index are frequently readily available in memory, covering indexes are the usually the ultimate in query resolution.

Below is a simple example about best index creation approach.

Suppose we have a table with 4 columns like below:

  create table tableT   
    (   
    id int IDENTITY(1,1) PRIMARY KEY,   
    [name] varchar(20),   
    code varchar(20),   
    date datetime   
    )   

Insert thousands of data into this table.

We have a most used query like below:

   SELECT * FROM tableT WHERE name='name1'  

Create several indexes as below to improve the query performance.

     --create NONCLUSTERED INDEX  
 create index name_index on tableT(name)   
  
 --create group index  
 create index name_index2 on tableT(name,code,[date])   
  
 --create covering index  
 create index name_index3 on tableT(name)include(id,code,[date])  

You could refer more details from below link:
Using Covering Indexes to Improve Query Performance

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A covering index is not a special type of index. An index is only covering in relation to a specific query.

A clustered index is always covering (since the leaf pages are the data pages), but one normally only talks about covering indexes in the context of non-clustered index.

Consider a query like this:

SELECT a, b FROM tbl WHERE c = 354

Assume that the clustered index is on a alone.

An index like this is a covering index for this query:

CREATE INDEX ix1 ON tbl (c, a, b)

as is

CREATE INDEX ix1 ON tbl (c) INCLUDE (a, b)

But also this one:

CREATE INDEX ix1 ON tbl (c) INCLUDE (b)

Because a is the key in the clustered index, is implicitly included in all non-clustered indexes,, because it serves as a row-locator.

On the other hand, this is not a covering index for this query:

CREATE INDEX ix1 ON tbl (c) INCLUDE (a)

To be able to read the value of a, SQL Server needs to access the data pages for the clustered indexes. Whereas in the other cases, the query can be served from the non-clustered index alone.

Having an index that covers a query can give a big performance boost. However, I'm not wholly enthusiastic of adding all columns in a SELECT list to make an index covering. Users come with new requirements, and you add d to the SELECT and the index is no longer covering.

Answer 3

@Erland Sommarskog ,
May I ask if there is a performance different between this approach (clustered): CREATE INDEX ix1 ON tbl (c, a, b)
and any of those (nonclustered with include): CREATE INDEX ix1 ON tbl (c) INCLUDE (a, b) / CREATE INDEX ix1 ON tbl (c) INCLUDE (b)?