Covering Index using Included Columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Josef Richberg » Covering Index using Included Columns

17 posts, Page 2 of 2

««1 2

Covering Index using Included Columns

Rate Topic

Display Mode

Topic Options

Author

Message

carl.anderson-1037280

Posted Thursday, February 11, 2010 9:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 08, 2011 11:22 AM
Points: 9, Visits: 97

This article is in regard to indexes with included column on heap tables. This means the table doesn't have a clustered index, right? In our data warehouse, all of our indexed tables use at least one clustered index (as per DBA mandate).

How does this article change if we're talking about a table with a clustered index?

Also, is there a compelling reason to use heap tables?

Thanks for the article!

Carl Anderson
Data Architect
Northwestern University EDW

Post #864144

sqlrunner

Posted Thursday, February 11, 2010 9:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 8:08 AM
Points: 29, Visits: 155

carl.anderson-1037280 (2/11/2010)

The leaf level of the non-clustered index would point, not to the heap table, but to the clustered index. The concepts still hold. You would make a covering index if your clustered did not satisfy your query. Regarding Heap Tables. Personally, I only put a clustered index on a table if there is a reason to, not just because there is a mandate. I prefer to have clustered indexes unique, although with 2005+ there is a uniquifier column just in case your index is not unique by key. If there are primary/foreign key relationships, then you have a clustered index.

Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner

Post #864150

Oleg Netchaev

Posted Thursday, February 11, 2010 9:37 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:39 PM
Points: 1,662, Visits: 1,708

RichardBo (2/11/2010)

What does SARG mean?

The best possible prelude will be Gail's own excellent article titled Introduction to Indexes. It has 3 parts, first was published here last September and others a little later. The url to the first part is http://www.sqlservercentral.com/articles/Indexing/68439/. Gail's article includes the link to Brad Mcgehee article about SARGable predicates.

Oleg

Post #864154

TravisDBA

Posted Thursday, February 11, 2010 12:05 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:23 AM
Points: 1,288, Visits: 2,996

Although using INCLUDE columns in an Index is not practical in all situations, using them to eliminate a Bookmark Lookup (RID) in an execution plan in a lot of situations I have generally found to be a good thing in query response times. Grant Fritchey has spoken on this in the past as well and he is an expert on Query Execution Plans IMHO.:)

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ... BigGrin

Post #864237

Fal

Posted Sunday, February 14, 2010 6:48 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 10:05 PM
Points: 584, Visits: 1,571

carl.anderson-1037280 (2/11/2010) In our data warehouse, all of our indexed tables use at least one clustered index (as per DBA mandate).

Carl Anderson
Data Architect
Northwestern University EDW

Not sure about a mandate, as a clustered index relates to how the data is physically stored on disk, hence you can only have one. Given that, it's best to have your clustered index on column(s) that will give a benefit in that regard.

My own preference is on column(s) that are required in common range lookups. So for unique IDENTITY() columns: No. But for datetime columns, or a "category" column, then you get the benefit of the physical order when reading pages BETWEEN <here> and <there>.

S.

Post #865341

BeerBeerBeer

Posted Thursday, March 11, 2010 5:53 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:17 PM
Points: 239, Visits: 68

So, if I have this clear in my head, if I have a query that joins several tables and and I'm selecting columns from a few of those tables, I might consider putting covering indexes on the columns I am selecting from a table in conjunction with it's SARG from the where clause.

As soon as I add another column to my query that is not in one of the covering indexes I would expect my execution time to return to the pre-covering index speed as the SQL engine is now required to go all the way back to the table for the extra column data.

Makes sense to me

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Post #881437

Lexa

Posted Friday, September 23, 2011 7:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 7:48 AM
Points: 152, Visits: 464

Hi,

I've a follow up question regarding this discussion. Query below which is called 10K a day causes of long PAGEIOLATCH_SH waits.

UPDATE table1.list_item
SET first_name =
CASE
WHEN ls.has_first_name = 1 THEN ls.first_name
ELSE li.first_name
END,
last_name =
CASE
WHEN ls.has_last_name = 1 THEN ls.last_name
ELSE li.last_name
END,
address_1 =
CASE
WHEN ls.has_address_1 = 1 THEN ls.address_1
ELSE li.address_1
END,
address_2 =
CASE
WHEN ls.address_2 is not null THEN ls.address_2
ELSE li.address_2
END,
city =
CASE
WHEN ls.has_city = 1 THEN ls.city
ELSE li.city
END,
state =
CASE
WHEN ls.has_state = 1 THEN ls.state
ELSE li.state
END,
zip_code =
CASE
WHEN ls.has_zip_code = 1 THEN ls.zip_code
ELSE li.zip_code
END,
country =
CASE
WHEN ls.has_country = 1 THEN ls.country
ELSE li.country
END,
home_phone =
CASE
WHEN ls.has_home_phone = 1 THEN ls.home_phone
ELSE li.home_phone
END,
work_phone =
CASE
WHEN ls.has_work_phone = 1 THEN ls.work_phone
ELSE li.work_phone
END,
cell_phone =
CASE
WHEN ls.has_cell_phone = 1 THEN ls.cell_phone
ELSE li.cell_phone
END,
got_money =
CASE
WHEN ls.got_money = 1 THEN ls.got_money
ELSE li.no_money
END,
has_mansion =
CASE
WHEN ls.has_mansion = 1 THEN ls.has_mansion
ELSE li.no_mansion
END

FROM @list_items ls
INNER JOIN table1.list_item li
ON ls.list_item_id = li.list_item_id
WHERE li.org_id = @org_id

The table1 is partitioned by org_id which is also a clustred index. I've created a non-clustered index on table1 like so

CREATE NONCLUSTERED INDEX [IX_list_item_list_item_id_org_id] ON [table1]
(
[list_item_id] ASC,
[org_id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, FILLFACTOR = 80, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

and I still see some performance issues.
I'm considering #1. adding list_item_id to the clustred index, so I'll have org_id and list_item_id as a composit clustered index or #2. add some INCLUDE columns to IX_list_item_list_item_id_org_id index like has_work_phone, has_cell_phone since they are bit types. I can't add all the columns in the "INCLUDE" that are involved in the update (First Name, address, etc.) due to size and that would be too much. What do you think?

Thanks in advance

Post #1180099

« Prev Topic | Next Topic »

17 posts, Page 2 of 2

««1 2

Permissions