Covering Index

Question

Covering Index

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 15, 2017 at 3:12 pm

#319666

When and how do you use a covering Index?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Is there anything different about scripting out a coverard index?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First question, in your own words, what is a covering index?

As far as scripting out a covering index, you do it the same way you do any other index.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.

For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)

If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).

If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.

If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Welsh Corgi - Wednesday, March 15, 2017 3:13 PM
Is there anything different about scripting out a coverard index?

That is what I thought.

Make sure the scriptoption is set to true

You can easily script theindex by using Object Explorer ->Databases -> Database -> Tables - > Indexes -> right-click on index- > Create Script

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ScottPletcher - Wednesday, March 15, 2017 3:43 PM
A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.
For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)
If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).
If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.
If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.

Scott, I wasn't asking you, that question was for the OP. I wanted to see if he understood what a covering index was.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Welsh Corgi - Wednesday, March 15, 2017 4:03 PM
Welsh Corgi - Wednesday, March 15, 2017 3:13 PM
Is there anything different about scripting out a coverard index?
That is what I thought.
Make sure the scriptoption is set to true
You can easily script theindex by using Object Explorer ->Databases -> Database -> Tables - > Indexes -> right-click on index- > Create Script
Thanks.

Lynn,

I know what a covering index is.

I wanted to make sure all bases are covered.

You want to make sure I know what a covering index is? lol

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Thanks for your input.

What's the definition of a covering index? And when do you use one, or the other, or both?

A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.

Covering indexes are often used to improve query performance if needed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I just wanted to articulate it properly.

Are you testing me? lol
Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Lynn Pettis - Wednesday, March 15, 2017 5:28 PM
ScottPletcher - Wednesday, March 15, 2017 3:43 PM
A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.
For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)
If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).
If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.
If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.
Scott, I wasn't asking you, that question was for the OP. I wanted to see if he understood what a covering index was.

Is this a test? lol

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

ScottPletcher - Wednesday, March 15, 2017 3:43 PM
A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.
For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)
If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).
If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.
If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.

Thank you Scott for being positive.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Lynn Pettis - Wednesday, March 15, 2017 5:28 PM
ScottPletcher - Wednesday, March 15, 2017 3:43 PM
A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.
For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)
If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).
If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.
If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.
Scott, I wasn't asking you, that question was for the OP. I wanted to see if he understood what a covering index was.
I do not intend for every thing to be a test question.
Thank you.😀

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

What makes you think I am a he not a she?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Nothing, I use he as a generic. There is nothing offensive in that in my mind. I have lived with being called she until I take the time to correct the individual otherwise.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Welsh Corgi - Wednesday, March 15, 2017 5:47 PM
Lynn Pettis - Wednesday, March 15, 2017 5:28 PM
ScottPletcher - Wednesday, March 15, 2017 3:43 PM
A covering index is an index that has all the columns needed to satisfy, or "cover", a given query.
For example, say I have a table with 20 columns, col1 thru col20.
clustered on col1 (ix1)
nonclustered index on ( col2 ) (ix2)
If I write this query:
SELECT col2, col1
FROM table_name
ORDER BY col2
Index ix2 is a perfect covering index, because it has all the columns needed to satisfy the query (the clus column(s) are always included in every index, so col1 is present also).
If you wrote this query:
SELECT col2, col1, col3
FROM table_name
ORDER BY col2
Only the clustering index covers this query, so SQL would have to scan the whole table.
If you modified IX2 to be:
nonclustered index on ( col2 ) include ( col3)
Then ix2 would again cover the query.
Scott, I wasn't asking you, that question was for the OP. I wanted to see if he understood what a covering index was.
Is this a test? lol

Yes, it is. It is based on the years that Welsh Corgi has been on this site and the questions and responses given in the past. Several of us wonder if there are different people all sharing a single account over time.