How To Get Table Row Counts Quickly And Painlessly
-
If an index is available, it WILL use it [though not always the primary key].
-
It worked .
-
I thought
SELECT count(1)
FROM mytable
WITH (nolock)
was the preferred method, and certainly easier to remember
-
I remember seeing this, and ending up switching back to count(*), but I cannot remember why - it may have been for accuracy problems, and I am not sure which version of SQL I was using at the time (I know it was 2000 or 2005). I also wonder if Microsoft would have the query engine do a table/index scan if it doesn't really have to - select count(*) FROM '1 table' is a relatively common operation. My gut tells me if it is doing a scan, that is because it actually *needs to* do a scan. Of course if you don't care if you occasionally see 4,815,162,341 rows when there are actually 4,815,162,342 rows, then this is a good trick to know.
-
I had always heard that you should do a "select count(1) from table".
http://www.techonthenet.com/sql/count.php
"TIP: Performance Tuning
Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields."
-
sqlservercentral (9/2/2009)
I find it baffling that the query planner can't provide a way to do count(*) quickly in the common case. It does so many other clever things; this one seems fairly straightforward to implement by comparison, and there's obviously a need for it, as the existence of this article shows.The server is just doing what we ask: if you ask for COUNT(*), that's what you get - a count of the number of rows returned by a full scan of all the records, at the current transaction isolation level.
The optimizer will choose an efficient method to implement the request (whether that be an IAM-based scan, or a full scan of an index). Reading row counts from system tables or dynamic views is a different request entirely.
Both are valid operations, but there are subtle differences because we are asking for different things.
Paul
-
wbrianwhite (9/2/2009)
I had always heard that you should do a "select count(1) from table".http://www.techonthenet.com/sql/count.php
"TIP: Performance Tuning
Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields."
This is a myth.
SELECT COUNT(*) produces an identical plan, with identical performance (and results) to SELECT COUNT(1) or SELECT COUNT(-4.678923) or whatever in all circumstances. Same goes for EXISTS (SELECT 1 FROM versus EXISTS (SELECT * FROM. I personally prefer to use the star, but it makes no difference.
I heard a rumour once that COUNT(*) might perform differently from COUNT(1) on some early versions of Oracle, but I may be mis-remembering.
Paul
-
A compact way to get a usually-accurate row count for a table is:
SELECT row_count = SUM(row_count)
FROM sys.dm_db_partition_stats DPS
WHERE DPS.[object_id] = OBJECT_ID(N'dbo.TableName', N'U')
AND DPS.index_id IN (0, 1);
Paul
-
Personally, I like running the following, and adding the specific table name in the where clause if I need it.
Note the (nolock) on the tables. If you are importing large amounts of data, this query will hang if the table is locked for loading. I've seen this query be up to the second accurate, and accurate during a data load.
select name as target_table, cast(sys.partitions.rows as nvarchar(30)) as rows from sys.partitions (nolock)inner join sys.tables (nolock) on sys.tables.object_id = sys.partitions.object_id
order by name desc
-
There can be a bad downside to COUNT(*) that has gone unmentioned: by doing a scan on something (especially the entire table if it has no non-clustered index on it) you could be forcing a LOT of very useful pages out of the buffer pool, leading to reduced performance for many seconds or even minutes as current data is reacquired from disk. Bad ju-ju there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Mathew Michuta (9/2/2009)
Note the (nolock) on the tables. If you are importing large amounts of data, this query will hang if the table is locked for loading. I've seen this query be up to the second accurate, and accurate during a data load.select name as target_table, cast(sys.partitions.rows as nvarchar(30)) as rows from sys.partitions (nolock)inner join sys.tables (nolock) on sys.tables.object_id = sys.partitions.object_id
order by name desc
That's fine, but:
a) If you are going to use the NOLOCK (or READUNCOMMITTED) table hint, you should use the WITH (NOLOCK) form. To quote BOL: Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
b) The query accesses only system tables and a system TVF - neither of which acquire normal locks (latches protect system structures).
You can see this by running the query inside a transaction (at REPEATABLE READ or above) and checking the sys.dm_tran_locks DMV:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
select name as target_table, cast(sys.partitions.rows as nvarchar(30)) as rows from sys.partitions
inner join sys.tables on sys.tables.object_id = sys.partitions.object_id
order by name desc
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
ROLLBACK;
Notice that I have removed the NOLOCK hints. Try adding TABLOCKX or anything else that takes your fancy - nothing changes 🙂
Paul
-
if just handle one table, and with some filter.
select count(1) from
where ...
it will run quickly and simple in my opinion.
-
Is this the quick way to get row count (other than [font="Courier New"]select count(1) from [TableX];[/font])painlessly?
Dinesh Mandal
-
What if you would like to query using a where clause?
For example...
SELECT COUNT(*) FROM books WHERE title LIKE '%Harry Potter%'
Or...
SELECT COUNT(*) FROM invoices WHERE deleted = 0
I could be wrong but I believe that querying the sysobjects is a great way for a DBA to find out table sizes but may not be as useful to a developer, who will probably want more complex results?
-
if you use SSMS, you can right-click on the table and choose properties. Within the properties window there is a section for storage which has an entry for the table's rowcount.
Not sure what runs behind this, but it is a lot quicker than select count(*) from table for a table with many rows.
Tom
Life: it twists and turns like a twisty turny thing
Viewing 15 posts - 16 through 30 (of 108 total)
You must be logged in to reply to this topic. Login to reply