number of rows in big table

问题:

SELECT COUNT(*) FROM BigTable_1

Which way I should to use to get number of rows in table if I have more than 1 billion of rows?

UPDATE: For example, if we have 'a timeout problem' with the query above is there any way to optimize it? How to do it quicker?

回答1:

If you need an exact count, you have to use COUNT (*)

If you are OK with a rough count, you can use a sum of rows in the partitions

SELECT SUM (Rows)
FROM sys.partitions
WHERE 1=1
And index_id IN (0, 1)
And OBJECT_ID = OBJECT_ID('Database.schema.Table');

If you want to be funny with your COUNT, you can do the following

`select COUNT (1/0) from BigTable_1`

回答2:

A very fast ESTIMATE:

select count(*) from table

But don't execute! Highlight the code, press ctl-l to bring up the query plan. Then hover over the leftmost arrow. A yellow box appears with the estimated number of rows.

You can query system tables to get the same data, but that is harder to remember. This way is much more impressive to onlookers.

:)

回答3:

You can use sys.dm_db_partition_stats.

select sum(row_count)
from sys.dm_db_partition_stats
where object_id = object_id('TableName') and  index_id < 2

回答4:

Depending on your concurrency, speed, and accuracy requirements, you can get an approximate answer with triggers. Create a table

CREATE TABLE TABLE_COUNTS(TABLE_NAME VARCHAR, R_COUNT BIGINT DEFAULT 0); 
INSERT INTO TABLE_COUNTS('BigTable_1', 0); 

(I'm going to leave out adding a key, etc., for brevity.)

Now set up triggers.

CREATE TRIGGER bt1count_1 AFTER INSERT ON BigTable_1 FOR EACH ROW 
BEGIN 
UPDATE TABLE_COUNTS SET R_COUNT=R_COUNT+1 WHERE TABLE_NAME='BigTable_1';
END;

A corresponding decrement trigger goes on DELETEs. Now instead of a COUNT, you query the TABLE_COUNT table. Your result will be a little off in the case of pending transactions, but you may be able to live with that. And the cost is amortized over all of the INSERT and DELETE operations; getting the row count when you need it is fast.

回答5:

Try this:

select sum(P.rows) from sys.partitions P with (nolock)      
join sys.tables T with (nolock) on P.object_id = T.object_id        
where T.Name = 'Table_1' and index_id = 1

it should be a lot faster. Got it from here: SELECT COUNT(*) FOR BIG TABLE

回答6:

Your query will get the number of rows regardless of the quantity. Try using the query you listed in your question.

回答7:

There's only 1 [accurate] way to count the rows in a table: count(*). sp_spaceused or looking at the statistics won't necessarily give you the [a?] correct answer.

回答8:

if you've got a primary key you should be able to do this:

select count(PrimaryKey) from table_1