Getting a SQL Server RowCount Without doing a Table Scan
来源:互联网 发布:日语单词背诵软件 编辑:程序博客网 时间:2024/06/03 12:53
Written By: Andy Novick -- 8/21/2006
Problem
Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary to use a SELECT count(*) query on the rows in a table to get the row count.
Solution
Thanks to Andrew Novick at Novick Software here is the answer. SQL Server keeps the row count in sysindexes and it can be retrieved there. The key is to select the correct record from sysindexes. Sysindexes is a system table that exists in every database.
SQL Server maintains at least one row in sysindexes for every user table. A few of the most important columns are:
Column Data Type Description
id int ID of the table referred to by this row
indid int See the text that follows...
rowcnt bigint Number of rows in the index
The indid column tells us what part of the table structure this row of sysindexes is referring to:
indid value Description
0 Table data when there is no clustered index
1 Refers to the clustered index
2 - 254 Non-clustered indexes
255 Text or Image data pages
A table will only have an entry in sysindexes with an indid value of for 0 or 1, never both. That's the entry that we're interested in because its rowcnt field gives us the number of rows in the table. Here is a query that shows the table, index and indid from the pubs database:
USE pubs
GO
SELECT so.[name] as [table name]
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
Here are the results:
table name Index Name indid
authors UPKCL_auidind 1
discounts NULL 0
employee employee_ind 1
jobs PK__jobs__117F9D94 1
pub_info UPKCL_pubinfo 1
publishers UPKCL_pubind 1
roysched NULL 0
sales UPKCL_sales 1
stores UPK_storeid 1
titleauthor UPKCL_taind 1
titles UPKCL_titleidind 1
As you can see from the results, most of the indexes are clustered (indid=1) but a few tables such as discounts don't have a clustered index (indid=0).
I started this tip with "If you don't need an exact answer..." That's because there are times when rowcnt is not the exact number of records in the table.
This can be corrected by updating statistics on the table with:
dbcc updateusage
go
Here's the CREATE FUNCTION script for udf_Tbl_RowCOUNT
CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
/*
* Returns the row count for a table by examining sysindexes.
* This function must be run in the same database as the table.
*
* Common Usage:
SELECT dbo.udf_Tbl_RowCOUNT ('')
* Test
PRINT 'Test 1 Bad table ' + CASE WHEN SELECT
dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
THEN 'Worked' ELSE 'Error' END
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
***************************************************************/
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
GRANT EXECUTE ON [dbo].[udf_Tbl_RowCOUNT] TO PUBLIC
GO
Let's use it:
use pubs -- assuming the UDF was created in pubs
go
SELECT [name]
, dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [name]
GO
Here are the results:
name Row Count
authors 24
discounts 3
employee 43
jobs 14
pub_info 8
publishers 8
roysched 86
sales 21
stores 6
titleauthor 25
titles 18
Next Steps
- Getting a SQL Server RowCount Without doing a Table Scan
- sql server中clustered index scan,table scan,index scan
- How can I start a VM without getting a window?
- Executing ASPX pages without a web server
- SQL SERVER的ROWCOUNT关键字
- SQL SERVER的ROWCOUNT关键字
- SQL Server @@ROWCOUNT 、@@ERROR用法
- How to solve SQL Server Error 1222 i.e Unlock a SQL Server table
- A - Doing Homework again
- ArgumentException: Getting control 0's position in a group with only 0 controls when doing Repaint A
- SQL: CREATE a table from another table
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
- Is there a way to loop through a table variable in TSQL without using a cursor?
- Db file sequential read while doing full table scan?
- SQL Server中Rowcount与@@Rowcount的用法
- SQL Server中Rowcount与@@Rowcount的用法
- 《C 陷进和缺陷》----词法分析中的“贪心法”
- 基于PXA255的U-boot移植
- 程序员规划职业道路时需考虑的十个问题
- 让子窗口在任务栏显示
- 深刻理解指针类型
- Getting a SQL Server RowCount Without doing a Table Scan
- 华为,华三设备基本配置
- 很好
- CCNA-Cisco-Chapter2
- 通用的JDBC的try…catch…finally
- C语言库函数
- 关于scanf()的缓冲区问题
- staitc_cast、dynamic_cast比较
- 哈哈~开博咯!!!