Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Covering Index using Included Columns Expand / Collapse
Author
Message
Posted Thursday, February 11, 2010 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:06 PM
Points: 10, Visits: 114
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
Posted Thursday, February 11, 2010 9:36 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 8:08 AM
Points: 29, Visits: 155
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
Post #864150
Posted Thursday, February 11, 2010 9:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:47 AM
Points: 1,676, Visits: 1,759
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
Posted Thursday, February 11, 2010 12:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
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. ..."
Post #864237
Posted Sunday, February 14, 2010 6:48 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, November 2, 2014 10:02 PM
Points: 506, Visits: 1,695
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
Posted Thursday, March 11, 2010 5:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:10 PM
Points: 241, 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

Post #881437
Posted Friday, September 23, 2011 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
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 »

Add to briefcase ««12

Permissions Expand / Collapse