RSS

SQL Tricks: How can I get table’s rows count ??

25 Oct

I am certain of that ask yourself what’s new in this post ??

The new thing that you will get the table’s rows count with another way. The new way which we will talk about it solve the main problem in the old on … Follow me:

In the past you can write the following T-SQL command to get the count or number of specified table’s rows:

SELECT COUNT(*) FROM dbo.[Customers]

If the table has many rows, the previous command will take a lot of time. You can test this by inserting 1,300,654 rows to a new table by the following sql script:

CREATE TABLE [TestTable]
(MyCol int);

———————-

DECLARE @x AS int;
SET @x = 1;
WHILE @x < 1300654
BEGIN
INSERT INTO dbo.TestTable VALUES (@x);
SET @x = @x + 1;
END

But … !!

But the best way to retrieve the count of any table’s rows is:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘TestTable’) AND indid < 2;

Finally, you can observe the difference between both of scripts by running the following script :

SET STATISTICS IO ON;
SELECT COUNT(*) FROM TestTable;
SELECT rows FROMsysindexes WHERE id = OBJECT_ID(‘TestTable’) AND indid < 2;

— You will see the next result, and please focus on underlined phrases:

(1 row(s) affected)
Table ‘TestTable’. Scan count 1, logical reads 2198, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘sysidxstats’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Notice:-

The sysindexes is a system table records the number of pages allocated and used by each table, index, and text or image column in a database.

The sysindexes table will have 1 to 255 rows for each table in the database. All rows for a given table will have the table’s object ID in their ID column.

 
Leave a comment

Posted by on October 25, 2011 in MS SQL Server

 

Tags: , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: