SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How efficient is your covered index?


How efficient is your covered index?

Author
Message
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4855 Visits: 1871
Comments posted to this topic are about the item How efficient is your covered index?

Simon Liew
Microsoft Certified Master: SQL Server 2008
gfey
gfey
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 39
Hi,

here a remark to the first solution. All thoughts concer the case of setting @carrier to null.

Since isnull creates an identity for carriertrackingnumber, the original query will return all rows where the other two criterias match.

On the other hand the resultset of first solution depends on the option ANSI_NULLS. If the option is set to ON the query returns only rows where carriertrackingnumber is null, otherwise it returns an empty resultset.

So in case of setting @carrier to null the solution never returns the same result as the original query.

Now the question is how to change the first solution to get the same behaviour?

Regargs
Gregor
sulaphen88
sulaphen88
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 31
How efficient is your covered index?
curious_sqldba
curious_sqldba
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14182 Visits: 3745
Thank you for sharing this article. Actually i had this question posted in forums also, so far no response, would like to know your thoughts please.

I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way?
james.of.rivendell
james.of.rivendell
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 126
Pretty sure it's called 'covering index' or less commonly 'cover index', but not 'covered index'. The index is not the one being covered.
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4855 Visits: 1871
gfey (8/12/2013)
Hi,
... On the other hand the resultset of first solution depends on the option ANSI_NULLS. If the option is set to ON the query returns only rows where carriertrackingnumber is null, otherwise it returns an empty resultset. ..

Regargs
Gregor

Hi Gregor,
Thanks for your comment.
Microsoft has announced ANSI_NULLS deprecation in SQL 2012. ANSI_NULLS OFF will not be supported in future version of SQL. Hence, I haven’t evaluated this as an option.

Simon Liew
Microsoft Certified Master: SQL Server 2008
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4855 Visits: 1871
hi curious_sqldba,
Its hard to describe your situation because query optimizer chooses a good enough plan depending on circumstance. For example, the number of records returned will have influence whether the query plan should utilize an index or might choose to do a table scan instead.

If the view is just a standard view containing just the tsql, then it would work very similarly to executing the query directly, except you can't have an ORDER BY in the view unless TOP, OFFSET or FOR XML is also specified.

I can only describe a hypothetical query below
select a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderDetail] b
on a.SalesOrderID = b.SalesOrderID
where a.SalesOrderID = 43659
order by a.SalesOrderID, a.CustomerID, b.CarrierTrackingNumber

1 record returned from SalesOrderHeader and 12 records returned from SalesOrderDetail. In this case, it would be efficient to perform an index seek on SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID, get SalesOrderID and CustomerID and use this table as a "base". In another word, the "base" table will provide input to the other join table SalesOrderDetail using a physical operation Nested Loops. SalesOrderDetail will use the provided SalesOrderID as a join condition to perform an index seeks on SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to get the 12 CarrierTrackingNumber. CarrierTrackingNumber needs to be sorted before the result is combined to the main resultset. Sort is not required for SalesOrderHeader because there's only 1 record returned.

This is an over-simplified explanation of such operation. I don't think I can cover in more detail in a discussion thread. Hope it helps.

Simon Liew
Microsoft Certified Master: SQL Server 2008
shankar.k
shankar.k
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 121
Hi Simon,
Thanks for the article. I have a basic question. My understanding of the indexes was to

> Create index on columns appearing in the WHERE clause
> If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.

Is my understanding correct? Can you please eloborate?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)SSC Guru (378K reputation)

Group: General Forum Members
Points: 378302 Visits: 42935
curious_sqldba (8/12/2013)

Thank you for sharing this article. Actually i had this question posted in forums also, so far no response, would like to know your thoughts please.

I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way?


I don't want to hi-jack this thread by answering a forum post. I'll take a look at your original post and answer there.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4855 Visits: 1871
shankar.k (8/12/2013)
Hi Simon,
Thanks for the article. I have a basic question. My understanding of the indexes was to

> Create index on columns appearing in the WHERE clause
> If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.

Is my understanding correct? Can you please eloborate?

Hi Shankar,
Your understanding is correct. Covered (or covering) index is a term decribing a certain technique that is used to improve query performance. It is not an index structure. That's why you might come across the term being used to describe numerous scenario as long as it implements the technique.

Technet article below has explaination on covering index. I have taken a snippet of the explaination

http://technet.microsoft.com/en-us/library/aa964133(v=sql.90).aspx
> Create index on columns appearing in the WHERE clause
- Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

http://technet.microsoft.com/en-us/library/jj835095.aspx
> If index has to be covering, add the columns appearing in the SELECT clause in the INCLUDED columns.
Performance gains are achieved when the index contains all columns in the query. The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Use index with included columns to add covering columns instead of creating a wide index key.

Simon Liew
Microsoft Certified Master: SQL Server 2008
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