index seek and scan - 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

Transact-SQL (2000)

index seek and scan

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 09:29:55

hey all...

got a question:
i got this table with 10241 rows in it.


create table Orgs
(
id int identity(1,1) PRIMARY KEY,
org_id uniqueidentifier
)

-- this gives index seek and takes 3 ms
select count(id) from OrgSearchResult
where id > 0

-- this gives index scan and takes 17 ms
select count(id) from OrgSearchResult

why??

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 09:43:21

The WHERE clause is probably giving the optimizer more of a clue to use the index. Have you tried using count(*)?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 09:50:47

i thought the same it's just not logical to me...
i have tried it. same result.

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 10:09:10

I confess I don't know either. But it's not like 14 ms is an eternity.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 10:21:26

no it's not an eternity, but it's the optimization freak in me that wants to know...

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 13:12:10

How can it be an index seek?

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 13:17:45

Statistics can be used by the optimizer to determine a row count for a particular key. It may appear as an index seek in the plan. And the optimizer can look at non-leaf index pages to determine counts too. Don't know if it does, but it's possible.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 13:22:54

if it's any help...


create table OrgSearchResult
(
id int identity(1,1) PRIMARY KEY,
org_id uniqueidentifier,
Name nvarchar(128), 
plan_year int, 
device_id uniqueidentifier
)

select count(id) as TotalRowNum 
from OrgSearchResult
where id > 0
  |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
            |--Clustered Index Seek(OBJECT:([CRM2].[dbo].[OrgSearchResult].[PK__OrgSearchResult__6BAFC5BE]), SEEK:([OrgSearchResult].[id] > 0) ORDERED FORWARD)

select count(id) as TotalRowNum 
from OrgSearchResult
  |--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
            |--Clustered Index Scan(OBJECT:([CRM2].[dbo].[OrgSearchResult].[PK__OrgSearchResult__6BAFC5BE]))

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources