Records number of a table

Dear Sir,

Instead of using SELECT count(*) From TABLE_NAME,

Is there any property of a Table that provide the total_records of this table?

Many Thanks

Xueliang

There's a quick way that returns a rowcount that is not necessarily 100% accurate, but that is good enough for most purposes, see below.

Chris

SELECT rows AS [RowCount]

FROM sysindexes

WHERE OBJECT_NAME(id) = '<Table Name>'

AND indid <= 1

|||

if u hv any mumeric fields ..u can chk out by diiference.. like auto id can sense this..or if product id p001 to p022 u can chk this too,

or the solutin by chris may work i hvnt tried..

|||

great job done sir..

sir, will it b common for any table?

will u plz explain syntax?

|||just wonder is there anything like row_counts_fuction(TABLE_NAME) ?

I think the SQL Server System should keep the total records number somewhere.

Thanks a lot

Xueliang|||

Chris Howarth rights. Statement in his answer doesn't count rows in table, but gets it from system table.

If you want function, you could create it:

create function row_counts_fuction(@.table_name varchar(50)) returns int

as

begin

declare @.result int

SELECT @.result = rows

FROM sysindexes

WHERE OBJECT_NAME(id) = @.table_name

AND indid <= 1

return @.result

end

Then:

select count(*) from Sales.SalesOrderDetail

--Returns 121317, works 0,21 sec

select dbo.row_counts_fuction('SalesOrderDetail')

--Returns 121317, works 0,00 sec

|||

>will u plz explain syntax?

Rather than me re-iterate what Microsoft say about sysindexes, let me point you towards the relevant topic in BOL - here you can see why the 'indid' column is included in the WHERE clause of the query.

http://msdn2.microsoft.com/en-us/library/ms190283.aspx

Chris

|||

If AUTO_CREATE_STATISTICS is not ON and not manually updated, then do I get Proper record

count from sysindexes ?

|||

To improve accuracy you should run DBCC UPDATEUSAGE before running the query.

See the following links for more info:

SQL Server 2000

http://msdn2.microsoft.com/en-gb/library/aa258283(sql.80).aspx

SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms188414.aspx

Apparently, according to MS, there should be no need to run the command in SQL Server 2005 as the statistics are maintained correctly.

Chris