Getting number of rows

Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2|||Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?
if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

use the COUNT() function:select count(*) from daTablethis query returns a single row consisting of a single column containing an integer which is the number of rows in the table

neat, eh? ;)|||if by "programmatically" you mean retrieving all the rows and returning them to your application program (asp, php, whatever), then the answer is a resounding yes!

use the COUNT() function:select count(*) from daTablethis query returns a single row consisting of a single column containing an integer which is the number of rows in the table

neat, eh? ;)

Just FYI,COUNT() ,when used in any form other than COUNT(*), ignores NULL values.So be cautious about that...|||ok..cool..i will try both techniques..my sql skills are pretty rusty anyway and need to learn different ways to do things

thanks everyone|||Selecting from sysindexes is technically faster than using count(*), as if a human would ever notice the difference, but the sysindexes value returned may not be accurate if statistics on the table are not up to date. And running UPDATE STATISTICS on all your tables will take a lot of time, so use the count(*) method if you need accuracy.|||if you have a large number of rows (say a few million) it's definitely better to go to sysindexes or call sp_spaceused (which hits sysindexes), as long as you don't need need the accuracy of count(*). count(*) is a pretty expensive way to get the count if there are many rows.|||the easiest way:
sp_spaceused <table name>|||I have been reviewing this thread here and have been trying to figure out how to use sysindex
rundra stated that I could use sysindex like this but would I use this command verbatim with exception of the 'table_name'?

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2|||You could use a cursor

Why do you need all the counts btw?

just kidding about the cursor|||if you have a large number of rows, but you don't actually need an accurate answer, then you can get away with not using COUNT(*)

... but then i have to ask, why do you care what the approximate number is?

why don't you just print "the answer is... um... very large"

i'm really curious under what circumstances you don't care about the actual count but do care about some other number that might not be anywhere near the correct answer|||I need the count for my paging system...

So, if I have a 1000 rows and my page size is 20 then my total pages will be 50. Is there a way that I can get an accurate count using the sysindex?|||an accurate count for a paging algorithm?

my advice: don't bother

nobody is gonna page through a paged result set all the way to the end|||an accurate count for a paging algorithm?

my advice: don't bother

nobody is gonna page through a paged result set all the way to the end

quite right. and even if you use count(*), it's only accurate at the instant the query is executed.

some other process can come along behind you and insert/delete, making the "accurate" value you fetched with the count(*) method no longer correct.