Solved

why not use the index?

Posted on 2014-12-02
4
157 Views
Last Modified: 2014-12-24
I have this query
I created an index

no difference analyzing statistics and execution plan, why?
I'm using a table of the AdventureWorks BD

use
AdventureWorks

--1 Obtener plan de ejecucion estimado
set statistics io on
      SELECT TOP 10
      p.ProductID,
      p.[Name],
      p.StandardCost,
      p.[Weight],
      ROW_NUMBER() OVER (ORDER BY p.Name DESC) AS RowNumber
      FROM Production.Product p
      WHERE p. StandardCost >150
set statistics io off

--(10 row(s) affected)
--Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

create NONCLUSTERED index  idx_Producto_StandardCost ON Production.Product (StandardCost)
0
Comment
Question by:enrique_aeo
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40477736
The index idx_Producto_StandardCost is for only one column:  StandardCost.

The query references four columns:  ProductId, Name, StandardCost, Weight.

Therefore, the index does not 'cover' all four columns the query needs, i.e. is not a Covering Index, and can't be used by the query.

So, if you want to speed up this query, then the index has to include all four columns.   Keep in mind that this can be at the expense of other SELECT queries, and definately INSERT-UPDATE-DELETEs.
0
 

Author Comment

by:enrique_aeo
ID: 40477765
can you give me T-SQL code please
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40477784
>can you give me T-SQL code please
Explain what you wish to do.
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40478331
You don't have any physical reads, only logical reads and that means SQL Server engine already has the records in memory so is going directly to cache instead of disk.
Also the number of scan is 1 that means no need for use the index. The engine is smart enough to know if it worthily or not to use an index. With 10 records that may be all in a single page I would say that you need to test it with a lot more data (thousands of records).
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question