Fastest way to count exact number of rows in a very large table?

by

Simple answer:

  • Database vendor independent solution = use the standard = COUNT(*)
  • There are approximate SQL Server solutions but don’t use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

More Related Contents:

  1. What are the options for storing hierarchical data in a relational database?
  2. Is there any rule of thumb to construct SQL query from a human-readable description?
  3. How can I list the tables in a SQLite database file that was opened with ATTACH?
  4. In MySQL queries, why use join instead of where?
  5. Doing a WHERE .. IN subquery in Doctrine 2
  6. Transpose latest rows per user to columns
  7. Key value pairs in relational database
  8. T-SQL Cast versus Convert
  9. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  10. SQL – How to transpose?
  11. Splitting the string in sql server
  12. How does table alias names affect performance?
  13. Performance issue in using SELECT *? [duplicate]
  14. Need to list all triggers in SQL Server database with table name and table’s schema
  15. What is the purpose of constraint naming
  16. How can I do a BEFORE UPDATED trigger with sql server?
  17. Drop all tables command
  18. How to find a table having a specific column in postgresql
  19. Deferrable Constraints in SQL Server
  20. can we list all tables in msaccess database using sql?
  21. INSERT COMMAND :: ERROR: column “value” does not exist
  22. Comma separated values in a database field
  23. Can SQL Server Express LocalDB be connected to remotely?
  24. What can cause an Oracle ROWID to change?
  25. SQL: Using NULL values vs. default values
  26. Why don’t DBMS’s support ASSERTION
  27. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  28. Should I commit or rollback a read transaction?
  29. Why are foreign keys more used in theory than in practice?
  30. What is difference between foreign key and reference key?

Leave a Comment