Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covering Index using Included Columns


Covering Index using Included Columns

Author
Message
carl.anderson-1037280
carl.anderson-1037280
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 127
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

sqlrunner
sqlrunner
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 159
carl.anderson-1037280 (2/11/2010)
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



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
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 3069
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.Smile

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1803
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.
BeerBeerBeer
BeerBeerBeer
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 74
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

Lexa
Lexa
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 508
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search