[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

10774: index clustered is better?? always?

Hi experts, because in this example is better to create a clustered index

--1. Crear una tabla temporal cargada con registros, para ello, ejecutar el siguiente
use AdventureWorks
GO
      Select * into
      PODTemp
      from Purchasing.PurchaseOrderDetail

--2. Para evaluar la cantidad de scans, logical reads, phisical reads, etc., ejecutar el
set statistics io on
      select * from PODTemp
      where productid between 500 and 510
      order by productid
set statistics io off
      --Table 'PODTemp'. Scan count 1, logical reads 78,

--3. Crear un índice nonclustered.
Create Nonclustered Index IDX1 on dbo.PODTemp (ProductId)

--4
--Volver a ejecutar el script del paso 2 y guardar la información en el tab messages.
set statistics io on
      select * from PODTemp
      where productid between 500 and 510
      order by productid
set statistics io off
--Table 'PODTemp'. Scan count 1, logical reads 78,

--5
--Ahora, se elimina y se creamos un índice clustered, para lo cual se ejecutan las siguientes sentencias (en el orden como figuran en cada fila)
      Drop Index IDX1 on dbo.PODTemp
      
      Create Clustered Index IDX1 on dbo.PODTemp (ProductId)

--6
set statistics io on
      select * from PODTemp
      where productid between 500 and 510
      order by productid
set statistics io off

--Table 'PODTemp'. Scan count 1, logical reads 8,
0
enrique_aeo
Asked:
enrique_aeo
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>where productid between 500 and 510
this is what is called a "range query"

a clustered index on that field will organise the records with the same values together (in the same page blocks), and hence such range queries will work "best" (least reads) when supported by such a index.

this also is good when you have several records with the same value (like a invoice table with the clustered index on customer id )
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now