What are 3 ways to get a count of the number of records in a table in

Question

What are 3 ways to get a count of the number of records in a table in sql server?

Sachin Singh · Answer

select count(*) from tableName select count(1) from tableName select count(PKColumn) from tableName Please check the execution plan for each then select the good one.

Rajanikant Hawaldar · Answer

1) Count(*) and Count(1) are same but Count(1) is faster so use Count(1) SELECT Count(1) FROM TableName 2) We can use SQL Server catalog views with the following dynamic management views: sys.tables – populates the list of tables. sys.indexes – populates the list of indexes of the table. sys.partitions – populates the rows of each partition. This approach is faster than the COUNT function. To get the count of rows run the following script: SELECT a.NAME c.NAME Sum(b.rows) FROM sys.tables a INNER JOIN sys.partitions b ON a.object_id = b.object_id INNER JOIN sys.indexes c ON b.index_id = c.index_id AND b.object_id = c.object_id WHERE a.object_id = Object_id('tblCustomer') AND c.index_id 3) The sp_spaceused procedure along with the rows count provides the following details: Name – the Table Name Rows – the Count of the rows in a table. Reserved – the total reserved space for a table. Data – the total space used by the table. Index_size – the total space used by the index. Unused – the total reserved space for a table that is not used. The syntax is: EXEC Sp_spaceused 'database_name.schema_name.table_name' Example query:EXEC Sp_spaceused 'WideWorldImportors.dbo.tblCustomer'