Index Scan Question.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Index Scan Question..

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-08 : 02:18:54

Hello,

I've got a table with about 30 columns, and it has a clustered index on the first column EmployeeID.

Why does the following query against a column that does not have an index indicate SQL Server is doing a clustered index scan instead of a Table scan ? Is SQL Server actually looking at every record in the table ?

SELECT field10
FROM EmployeeTable
WHERE field10 = 'somevalue'

Perhaps I just don't understand enough about what's going on yet.

Kevin

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 02:48:15

The leaf level of a clustered index is the data pages so a clustered index scan is actually scanning the data pages.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-08 : 04:46:30

Ok,

So an index seek is better than an index scan, right ?

Kevin

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 04:54:39

Yes.
Lots.

An index scan isn't using the index structure - it's searching the leaf level. For a non-clustered index it's faster than scanning data pages because it reads less pages.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GenerationWithoutName
Starting Member

26 Posts

Posted - 2003-08-08 : 16:27:48

i have a table with 2 millions rows.
which faster, index or join statement.
CREATE TABLE #tblCM
( CMNo varchar(10) NOT NULL PRIMARY KEY,
CMDate smalldatetime,
AssetCost decimal(12,2),
Deposit decimal(12,2),
Installment decimal(12,2),
EffRate decimal(7,4),
Tenor smallint,
IsNPP bit NOT NULL --> Flag
)
GO
CREATE TABLE #tblNPP
( NPPNo varchar(10) NOT NULL PRIMARY KEY,
NPPDate smalldatetime,
CMNo varchar(10) NOT NULL,
CONSTRAINT FK_tblCM_tblNPP FOREIGN KEY (CMNo)
REFERENCES #tblCM(CMNo)
)
GO
CREATE UNIQUE INDEX IDX_CMNo ON #tblNPP(CMNo)
WITH PAD_INDEX, FILLFACTOR=90

Well, i want query data in #tblCM that doesn't exist in #tblNPP.
should i use LEFT OUTER JOIN or NOT IN or NOT EXISTS Statement,
or i create index on #tblCM.IsNPP
( CREATE INDEX IDX_IsNPP ON #tblCM (IsNPP)
WITH PAD_INDEX, FILLFACTOR=90 )

WHICH FASTER???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-08 : 19:47:07

There's no way to say which one is faster without testing them. The same data with the same indexes on two different machines could perform differently.

Make sure when you test that you run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before you run each statement. This prevents caching from skewing the results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources