How To Find Number Of Records In Sql Table

Recently, I was working on a database operation improvement projection. One stored procedure in that location was causing issues. In its code, a query populated the Count of the rows and stored the value in a local variable. That query was scanning a large table. Due to that, resource utilization became significantly college. To prepare the issue, nosotros removed the faulty code and used the SQL Server catalog views to generate the Count of rows of the table.

In that location are various ways to count the number of rows in a table of SQL Server. This article will describe them for you to always choose the correct style to do it.

We can get the Count of rows of the table with any of the following methods:

Use COUNT() function.
Combining SQL Server catalog views.
Using sp_spaceused stored process.
Using SQL Server Management studio.

Let the states dig deeper.

Get row count using COUNT(*) or Count(1)

Nosotros can employ the COUNT(*) or COUNT(1) function – the results generated by these two functions are identical.

To get the row count, allow us start run the query using COUNT(*). For demonstration purposes, I have set the value of STATISTICS IO as ON.

          USE wideworldimporters  go   SELECT Count(*)  FROM   tblcustomer  go

Output:

IO statistics:

          Table 'tblCustomer'. Browse count i, logical reads 691, physical reads 315, page server reads 0, read-ahead reads 276, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob folio server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

As y'all tin see, the SQL Server has to perform 691 logical reads to satisfy the result.

Now, let us run the query using COUNT(ane):

          Employ wideworldimporters  go   SELECT Count(1)  FROM   tblcustomer  go

Output:

IO Statistics:

          Table 'tblCustomer'. Scan count one, logical reads 691, physical reads 687, folio server reads 0, read-alee reads 687, page server read-ahead reads 0, lob logical reads 0, lob concrete reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Again, SQL Server must perform 691 logical reads to satisfy the result.

Nosotros should mention that in that location is an opinion that the Count (1) is faster than the Count (*) function. However, every bit you tin run into in the higher up examples, the result sets and IO statistics are the aforementioned. Therefore, you can use whatever method to generate the row count of tables.

Pros:

The COUNT role populates an accurate number of rows from the table.

Cons:

When yous execute the COUNT function, it places a lock on the table. Other queries accessing the table accept to await until the result is generated. If you are working on a decorated system with a table having millions of rows, you'd meliorate avoid running the COUNT function during business hours, unless you take to populate the verbal row count of the table.

Combining SQL Server catalog views

Nosotros tin use SQL Server itemize views with the post-obit dynamic direction views:

sys.tables – populates the list of tables.
sys.indexes – populates the listing of indexes of the table.
sys.partitions – populates the rows of each sectionalisation.

To get the count of rows, run the post-obit 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 Bring together 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 < 2

Output:

The query populates the table proper name, alphabetize name, and full rows in all partitions.

Now, permit us review the IO Statistics:

          Table 'syssingleobjrefs'. Scan count 3, logical reads 6, physical reads 0, folio server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'sysidxstats'. Browse count one, logical reads vi, concrete reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Tabular array 'sysschobjs'. Browse count 0, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob concrete reads 0, lob folio server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Tabular array 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-alee reads 0. Tabular array 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-alee reads 0, lob logical reads 0, lob concrete reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'sysrowsets'. Browse count ii, logical reads 14, physical reads 0, folio server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob concrete reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

As you can come across, the query performs just 30 logical reads.

Pros:

This approach is faster than the COUNT function. It does not larn a lock on the user table, so you lot tin apply it in a busy arrangement.

Cons:

The method populates an approximate Count of rows. In the Microsoft documentation of sys.partitions, yous can see that the rows column brings the approximate number of rows for the partitions.

Thus, if you are looking for a query that brings the outcome faster than the COUNT part, you can use this one. Still, the outcome might be inaccurate.

Use sp_spaceused

The sp_spaceused procedure along with the rows count provides the following details:

Name – the Table Proper noun
Rows – the Count of the rows in a table.
Reserved – the full reserved space for a table.
Data – the total space used by the tabular array.
Index_size – the total space used by the alphabetize.
Unused – the total reserved infinite for a table that is not used.

The syntax is:

          EXEC Sp_spaceused 'database_name.schema_name.table_name'

The query:

          EXEC Sp_spaceused 'WideWorldImportors.dbo.tblCustomer'

Output:

Utilise SQL Server Direction Studio

To go the rows count of the tabular array, we tin use SQL Server direction studio.

Open SQL Server Management studio > Connect to the database instance > Expand Tables > Right-click on tblCustomer > Properties

To get the rows count of the table, we can use SQL Server Management Studio

In the Table Properties window, click on Storage . You volition see the Row count value on the right:

In the Table Properties window, click on Storage. You will see the Row count value on the right

Another choice to get the number of rows in a table comes with the SQL Complete SSMS Add together-in. With this enhancement, you can see the estimated number of rows in a hint when yous hover the mouse over a table name in the Object Explorer window. This way, you can get the necessary data in a visual mode without any additional efforts.