number of rows SQL Server

Question

How to count or know the number of rows a table has without scaning all the table, maybe using ROW_NUMBER?

count(primary key) will not scan all the table, so dont be afraid of using it. Check my answer for a more detailed info. — MestreLion, Mar 04 '11 at 21:36
@MestreLion - It **will** scan the whole table. With a CI the leaf level **is** the table. It needs to scan the leaf pages themselves as this information is not available in upper levels (intermediate level pages only have a key and pointer for each **page** in the level below they have no information on how many rows each child page contains) — Martin Smith, Mar 04 '11 at 22:28

The Scrum Meister · Accepted Answer · 2011-03-04T21:40:38.620

17

If you need a exact count, you will need to do a COUNT(*) which will scan the clustered index.

You can get a rough count using the sys.partitions schema, as shown here http://www.kodyaz.com/articles/sql-rowcount-using-sql-server-system-view-sys-partitions.aspx

Update: To get the count into a variable:

DECLARE @cnt INT;
SELECT @cnt = SUM(rows)
FROM sys.partitions
WHERE
  index_id IN (0, 1)
  AND object_id = OBJECT_ID('MyDB.dbo.MyTable');
SELECT @cnt;

edited Mar 04 '11 at 21:40

answered Mar 04 '11 at 21:25

The Scrum Meister

29,681
8
66
64

3

+1 The only answer so far to address the "without scanning all the table" part of the question. – Martin Smith Mar 04 '11 at 21:29
I red the info in the link, it says it is fast to use SQL Server system view (sys.partitions). Do you know if outperforms `select count(*) from table` for lets say a table with lots of rows (>100,000,000)? – edgarmtze Mar 04 '11 at 21:31
@darkcminor - It will easily out perform it as it just has to retrieve a number from the system tables as opposed to scanning and counting 100 million rows. (probably x00,000 logical reads depending on how many rows in a page) – Martin Smith Mar 04 '11 at 21:34
correct, very nice answer, do you care if I post the code from that page here? – edgarmtze Mar 04 '11 at 21:34
@darkcminor It will definitely be *faster*, since - at the very least - it won't need to place shared locks on the rows, however it will be less accurate. – The Scrum Meister Mar 04 '11 at 21:35
Also do you know a way to set a @variable this result? – edgarmtze Mar 04 '11 at 21:36
sys.partitions will be faster, but it wont be accurate. If all wou want is a rough estimative, go fo it. But for accuracy, theres no other way than COUNT(*). But dont worry: its an indexed count, not a full table scan – MestreLion Mar 04 '11 at 21:39

score 3 · Answer 2 · answered Mar 04 '11 at 21:24

3

SELECT COUNT(*) FROM Table

will return the number of rows

answered Mar 04 '11 at 21:24

Simon

9,197
13
72
115

score 3 · Answer 3 · answered Mar 04 '11 at 21:32

3

There is no ROW_NUMBER in SQL Server, just Oracle. Use:

SELECT COUNT(primary_key) FROM table

Where primary key the primary key column of your table.

Since its a primary key, its is already indexed, so SQL can count it without scanning the whole table (it uses a clustered index to be precise, which is much faster than a full table scan)

You could also use sys.indexes schema, but its not accurate, and you would need database admin priviledges to access, and your application database user is not supposed to have grants in that schema

answered Mar 04 '11 at 21:32

MestreLion

12,698
8
66
57

COUNT(PK) is actually slower than COUNT(*) – Simon Mar 04 '11 at 21:38
@simon I highly doubt it. Can you please provide sample code where it will be slower? – The Scrum Meister Mar 04 '11 at 21:41
@Simon: i dont see why, since internally SQL uses the primary key index to count the rows then COUNT(*) is used. If same index is used for both counts, performance should be pretty similar, if not the same. – MestreLion Mar 04 '11 at 21:42
Ok, so im seeing mixed bag here, but http://lists.mysql.com/maxdb/18486 shows one example. My bad - COUNT(ID) seems the way to go – Simon Mar 04 '11 at 21:49
(couldve sworn i read that somewhere - i even changed all my queries :() – Simon Mar 04 '11 at 21:59
Ok just reading more on the subject - choose the COUNT() carefully depending on your schema, DBMS and storage engine: http://mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ and http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better – Simon Mar 04 '11 at 22:14
SQL Server Specific : Count(*) can use any index (non-filtered) and will select the narrowest. Count(PK) must use an index with the PK in it - however, if the table is clustered on that PK, then the PK is contained within every NC index anyway - so SQL will then still choose the narrowest index to count. – Andrew Mar 04 '11 at 23:29
@Scrum the way to construct an example of a slower run counting the PK would be a non-clustered primary key that was quite wide vs counting a narrow non-clustered non-filtered index. – Andrew Mar 04 '11 at 23:31
1

@Andrew - That isn't the case (your last comment). `COUNT(PK)` just appears in the plan as `Count(*)` anyway. Try `create table #foo(wide char(800) primary key nonclustered,narrow int); create nonclustered index ix on #foo(narrow); insert into #foo SELECT object_id, object_id FROM sys.objects; select COUNT(wide) from #foo;` – Martin Smith Mar 04 '11 at 23:43
1

You're right, had to test some examples to see the exact optimization going on - tag on to your example select count(distinct wide) from #foo - I'm asking for the count of distinct values in the wide column. The query plan shows it accessing foo.ix only - which we know does not contain those values - so how can it guarentee the right answer? if I remove the PK aspect of that column, it then counts from the table. The optimizer is taking a short cut by saying - if its the PK, I know its unique, so its same as count(*), use the narrowest index again - unexpected but smart. – Andrew Mar 05 '11 at 11:52
@Andrew - BTW I just tested and `wide` doesn't even have to be the PK. It is sufficient that it is marked as `NOT NULL` for the narrower index to be used. If it is non nullable and unique (e.g. PK) then SQL Server will also use the narrow index for `select COUNT(DISTINCT wide) from #foo;` – Martin Smith Mar 05 '11 at 13:26
Whoops just realised that the 2nd part of my comment just repeats what you were already saying! – Martin Smith Mar 05 '11 at 14:10

score 3 · Answer 4 · answered Mar 04 '11 at 22:37

A little late to the party here, but in SQL Server 2005 on, you could also use the sp_spaceused stored procedure:

DECLARE @rowCount AS INT
DECLARE @spaceUsed TABLE(
    [Name] varchar(64), 
    [Rows] INT,
    [Reserved] VARCHAR(50),
    [Data] VARCHAR(50),
    [Index_Size] VARCHAR(50),
    [Unused] VARCHAR(50)
)
INSERT INTO @spaceUsed EXEC sp_spaceused 'MyTableName'
SET @rowCount = (SELECT TOP 1 [Rows] FROM @spaceUsed)
SELECT @rowCount AS 'Row Count'

I've gotten into the habit of using sp_spaceused in place of SELECT COUNT(*) FROM Table because it is much faster. It will most likely not be as accurate as COUNT(*), however.

MSDN: http://msdn.microsoft.com/en-us/library/ms188776.aspx

score 0 · Answer 5 · answered Mar 04 '11 at 21:25

0

I dont believe you mean this but ill give it a try:

select count(*) from table

answered Mar 04 '11 at 21:25

CloudyMarble

36,908
70
97
130

1

Again: this **will** scan the whole table (not recommended for tables with millions of rows....) – marc_s Mar 04 '11 at 21:44

number of rows SQL Server

5 Answers5

Linked