top of page

Non-clustered Index

Nonclustered indexes: copies stored in order we want, include the fields we want

CREATE INDEX IX_LastAccessDate_ID ON dbo.Users(LastAccessDate, Id)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SET STATISTICS TIME, IO ON;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Nonclustered index 를 쓸 때와 쓰지 않을 때 cpu 시간, 논리적 읽기가 많이 차이가 난다
-> The index covers the fields needed by the query, so we call it a covering index

Nonclustered index가 좋은것 같지만 148296row를 읽는다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Seek

"I'm going to jump to a row and start reading" -> jump to a spot but then may end up reading the whole entire table
A seek can start at the first row, and read the entire table.
하나의 값을 찍어 검색

Scan

"I'm going to start at either end of the object(might be either the start, or the end) and start reading" -> start at the end of the table and gonna read util I find enough row to satisfy of my query
A scan can start at one end of the table, and only read few pages
전체를 읽는것

-> seek가 많다고 좋은것이 아니고 scan이 많다고 좋은것이 아니다. how many rows each of them is reading and how may rows it's returning이 중요하다

 

 

 

 

from https://www.youtube.com/watch?v=eYXC_UnmrEY&list=PLGyh3dhppvKoEFryHIKmpOn_bwoIDSk0h&index=2

 

 

 

bottom of page