August 11, 2013 at 5:36 pm
Comments posted to this topic are about the item How efficient is your covered index?
Simon Liew
Microsoft Certified Master: SQL Server 2008
August 12, 2013 at 2:36 am
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
August 12, 2013 at 4:57 am
How efficient is your covered index?
August 12, 2013 at 10:28 am
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?
August 12, 2013 at 3:56 pm
Pretty sure it's called 'covering index' or less commonly 'cover index', but not 'covered index'. The index is not the one being covered.
August 12, 2013 at 5:31 pm
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
August 12, 2013 at 5:33 pm
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
August 12, 2013 at 8:28 pm
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?
August 12, 2013 at 9:39 pm
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
Change is inevitable... Change for the better is not.
August 12, 2013 at 10:25 pm
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
August 12, 2013 at 10:28 pm
Hi Simon,
Thanks for the reply. This clarifies.
Cheers,
Shankar
August 13, 2013 at 2:45 am
Thanks for the article.
August 13, 2013 at 6:08 am
Divine Flame (8/13/2013)
Thanks for the article.
You are welcome 🙂
Simon Liew
Microsoft Certified Master: SQL Server 2008
August 13, 2013 at 6:40 am
Interesting article.
I have index with 3 columns.
I get Index Seek with 2 columns in Seek Predicates.
And one column in Predicates: [T_Encounter].[sysDeleted] as [e].[sysDeleted]='N'
There are no hidden implicit conversions. sysDeleted and 'N' are both same type.
No mater what I do, I can't get sysDeleted column into Seek Predicates?
August 13, 2013 at 4:54 pm
romanilic (8/13/2013)
Interesting article.I have index with 3 columns.
I get Index Seek with 2 columns in Seek Predicates.
And one column in Predicates: [T_Encounter].[sysDeleted] as [e].[sysDeleted]='N'
There are no hidden implicit conversions. sysDeleted and 'N' are both same type.
No mater what I do, I can't get sysDeleted column into Seek Predicates?
Hi romanilic,
Are you able to post the table+index definition, sample data and the query here for testing?
Simon Liew
Microsoft Certified Master: SQL Server 2008
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply