count no. of rows - 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 2005 Forums

Transact-SQL (2005)

count no. of rows

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-13 : 20:41:22

Is it possible to count total no. of rows in a table without scanning whole table??

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-13 : 21:25:34

Do you mean "you" or "SQL Server"? SQL Server can scan a whole index if it's on a not null column (e.g. primary key). From the SQL perspective you just use count(*) and let the database work it out. Is that what you are asking?

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-13 : 23:27:47

quote:
Originally posted by LoztInSpace

Do you mean "you" or "SQL Server"? SQL Server can scan a whole index if it's on a not null column (e.g. primary key). From the SQL perspective you just use count(*) and let the database work it out. Is that what you are asking?

I mean when we select count(*) from tableA, it will scan whole table and the query time is very long if the table is huge...so that any way to do this in shorter query time??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-14 : 01:16:24

Don't you have a clustered index on the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Jillc
Starting Member

4 Posts

Posted - 2008-03-14 : 01:20:58

select o.name, i.rowcnt From sysobjects o inner join sysindexes i on o.id = i.id where indid in (0,1) and xtype = 'u' order by 1

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-03-14 : 04:00:36

if its a unique identity column generated by system
and if u have not deleted a value since.,
you can try this.,
select top 1 id from table order by (id)desc

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-14 : 06:21:03

quote:
Originally posted by Jillc

select o.name, i.rowcnt From sysobjects o inner join sysindexes i on o.id = i.id where indid in (0,1) and xtype = 'u' order by 1

Thanks for the reply. I have cluster index on the table. If I want to select count(*) from tableA where dtRecord < '1-March-2008', how to put the where clause in the sysobjects query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-14 : 11:38:11

Don't use the sysobjects query as it can be inaccurate.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-14 : 11:52:52

where dtRecord < '1-March-2008'
that pretty much guarantees a table scan since you are looking for everything but March.

Jim

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-16 : 21:21:19

quote:
Originally posted by juicyapple

I mean when we select count(*) from tableA, it will scan whole table and the query time is very long if the table is huge...so that any way to do this in shorter query time??

I don't think you can make any assumptions on how SQL Server establishes the row count. There is nothing to stop it using the PK index instead, or any other internal structures. Anything else you do is guesswork and using inaccurate internal structures. Count(*) is the only reliable way to count your rows, and the fastest. If you can think of a better way to do it, ask yourself why Microsoft have not implemented count(*) as that. You are wasting your time & energy even thinking about this stuff.

quote:
Originally posted by karthik_padbanaban

if its a unique identity column generated by system
and if u have not deleted a value since.,
you can try this.,
select top 1 id from table order by (id)desc

Not true. Identity columns can have gaps even if you have not deleted anything.

quote:
Originally posted by jimf

where dtRecord < '1-March-2008'
that pretty much guarantees a table scan since you are looking for everything but March.

That would make sense, unless there is an index on the date in which case an index scan would be a better option.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 05:00:55

quote:
Originally posted by juicyapple

quote:
Originally posted by Jillc

select o.name, i.rowcnt From sysobjects o inner join sysindexes i on o.id = i.id where indid in (0,1) and xtype = 'u' order by 1

Thanks for the reply. I have cluster index on the table. If I want to select count(*) from tableA where dtRecord < '1-March-2008', how to put the where clause in the sysobjects query?

You cant apply this where clause against sysindexes table

Select count(*) from table <where condition>

is more accurate in this case

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources