counting records

Hi,
I need to count records in a table to make server-side paging. COUNT(ID) is
rather expensive operation. Can I relay on a query returning number of rows
for a table based on rowcnt field in sysindexes table? I mean if this query
will return up-to-date number of rows.
thanks
PrzemoYou can rely on rowcnt in sysindexes to return a number that was accurate at
some point, but not necessarily at the time you query it.
Two things you can do:
1. Don't use COUNT(field_name), use COUNT(*) - using * allows sql server to
choose the smallest index to count
2. Use the NOLOCK hint on the table(s) being queried; while your query could
be inaccurate due to using NOLOCK, it's probably worth the performance
benefit in this case
"Przemo" wrote:

> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID) i
s
> rather expensive operation. Can I relay on a query returning number of row
s
> for a table based on rowcnt field in sysindexes table? I mean if this quer
y
> will return up-to-date number of rows.
> thanks
> Przemo|||If you run DBCC UPDATEUSAGE before the query.
HTH
Jerry
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID)
> is
> rather expensive operation. Can I relay on a query returning number of
> rows
> for a table based on rowcnt field in sysindexes table? I mean if this
> query
> will return up-to-date number of rows.
> thanks
> Przemo|||>> Can I relay on a query returning number of rows for a table based on
No, you cannot rely on the values in sysindexes. There are several known
instances where these values can be suspect. In some cases updating the
table statistics might help, but that is no way a guarantee for accuracy.
Anith|||When paging, is it really necessary to know beforehand how many total rows
are in the table or subset?
For example, would the following work?
select top 20 name, address, phone from customer where CustomerID >
@.PrevCustomerID
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
> Hi,
> I need to count records in a table to make server-side paging. COUNT(ID)
> is
> rather expensive operation. Can I relay on a query returning number of
> rows
> for a table based on rowcnt field in sysindexes table? I mean if this
> query
> will return up-to-date number of rows.
> thanks
> Przemo|||> select top 20 name, address, phone from customer
> where CustomerID > @.PrevCustomerID
You would need to ORDER BY CustomerID on that query.
"JT" wrote:

> When paging, is it really necessary to know beforehand how many total rows
> are in the table or subset?
> For example, would the following work?
> select top 20 name, address, phone from customer where CustomerID >
> @.PrevCustomerID
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:198952B1-B05A-4AAA-B1EE-E926DB23932A@.microsoft.com...
>
>|||I knew that, but it's a Friday afternoon, and I'm conserving keystrokes.
;-)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:D8EFDC52-4244-41AE-9078-2945ED2B74A3@.microsoft.com...
> You would need to ORDER BY CustomerID on that query.
>
> "JT" wrote:
>