0

Is there a better way instead of SELECT COUNT(*) statement to count the number of records in table?

Sometimes we have to count billions of records from temporary tables that are imported using bcp query.

Parshii
  • 58
  • 1
  • 13
  • 1
    have a look at this http://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table – Rohan Sep 20 '13 at 11:54
  • Thanks @R.S , I looked into it, I'll check the performance. – Parshii Sep 20 '13 at 12:26

2 Answers2

1

Using count(*) or count(some_column) is the fastest way to check for a tables record count.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • I've always done count(primaryKey) because I thought the "*" would add some overhead. But, I'm not positive. – L_7337 Sep 20 '13 at 11:53
  • 5
    `count(*)` and `count(some_column)` are not equivalent if the column is nullable. – Martin Smith Sep 20 '13 at 11:55
  • SELECT COUNT(*) statement makes a full table scan to return the total table's row count which can take an extremely long time for large tables, ya its good to count primary key, but here I am about records that touches billion count. @L_7337 – Parshii Sep 20 '13 at 11:56
  • @Parshii - You can get a figure that is likely pretty accurate from the metadata. See [the answer](http://stackoverflow.com/a/6069288/73226) linked to already by R.S. – Martin Smith Sep 20 '13 at 11:57
  • @MartinSmith: So is it normally better to use `Count(*)` instead of `Count(SomeColumn)` because the column could be nullable(now or in future)? Will sql-server automatically use the "cheapest" column if i pick one that is not nullable(like the PK-Column or any other) or if i use `*`? – Tim Schmelter Sep 20 '13 at 12:10
  • 1
    @TimSchmelter - I use `COUNT(*)` anyway as there is no benefit of using a column name (even the PK or other `NOT NULL` column) and it is shorter and (IMO) clearer. It would be more defensive as well for that reason. And yes `COUNT(1)` or `COUNT(*)` or `COUNT(PK)` all get the same plan (and show up as `COUNT(*)` in the plan) and SQL Server will use the narrowest index available to do the `COUNT`. – Martin Smith Sep 20 '13 at 12:13
  • @MartinSmith I like your last comment, I'll be searching more on how to increase the count speed. And I checked the link you posted in comments also. I'll be back soon to see good answers or I'll post If I found one. – Parshii Sep 20 '13 at 12:22
  • @Parshii - The only options are to use the figure from the metadata if you can live [with the possible inaccuracies](http://stackoverflow.com/questions/17446002/uncoditioned-count-vs-system-tables/17446047#17446047) or use `COUNT`. And the only thing you can do to optimise `COUNT` on a `#temp` table is to provide a narrow index. – Martin Smith Sep 20 '13 at 12:30
  • What about this query, I think its fine. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 @MartinSmith. Your comments on it? – Parshii Sep 20 '13 at 12:38
0

If you don't need to filter, the following query works well:

SELECT sum(rows) FROM SYS.PARTITIONS WHERE object_id=object_id('MY_TABLE') and index_id in(0,1)

That checkes the number of rows sql server is storing for that object. It can't return any data with the count, and there is no way to include a group by or where.

Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • Thanks Vulcronos it is really a fast line of code. Earlier I was using SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2. – Parshii Sep 24 '13 at 10:31