IF EXISTS (SELECT name FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[row_count]') )
DROP FUNCTION [dbo].[row_count]
GO
CREATE FUNCTION dbo.row_count (@table_name sysname)
-- @table_name we want to get count
RETURNS bigint
/*
-------------------------------------------------------
-- Function Name: row_count
-- Author: Mohamed Hassan
-- Email: moh_hassan20@yahoo.com
-- Development Date: 08/11/2008
-- Version: 1.0
-- Description: Return row count of the whole table, as a replacement for count(*) , give extra performance at least 70% over , than count(*) for large tables with millions of rows
-- SQL Server: SQL server 2005
-- Usage Example: select dbo.row_count ('Sales.SalesOrderDetail')
-- Copyright:
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
-------------------------------------------------------
*/
AS
BEGIN
DECLARE @nn bigint -- number of rows
IF @table_name IS NOT NULL
BEGIN
SELECT @nn = sum( p.rows )
FROM sys.partitions p
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
p.index_id in(0,1) -- 0 heap table , 1 table with clustered index
and p.rows is not null
and a.type = 1 -- row-data only , not LOB
and p.object_id = object_id(@table_name)
END
RETURN (@nn)
END
GO
DROP FUNCTION [dbo].[row_count]
GO
CREATE FUNCTION dbo.row_count (@table_name sysname)
-- @table_name we want to get count
RETURNS bigint
/*
-------------------------------------------------------
-- Function Name: row_count
-- Author: Mohamed Hassan
-- Email: moh_hassan20@yahoo.com
-- Development Date: 08/11/2008
-- Version: 1.0
-- Description: Return row count of the whole table, as a replacement for count(*) , give extra performance at least 70% over , than count(*) for large tables with millions of rows
-- SQL Server: SQL server 2005
-- Usage Example: select dbo.row_count ('Sales.SalesOrderDetail')
-- Copyright:
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
-------------------------------------------------------
*/
AS
BEGIN
DECLARE @nn bigint -- number of rows
IF @table_name IS NOT NULL
BEGIN
SELECT @nn = sum( p.rows )
FROM sys.partitions p
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
p.index_id in(0,1) -- 0 heap table , 1 table with clustered index
and p.rows is not null
and a.type = 1 -- row-data only , not LOB
and p.object_id = object_id(@table_name)
END
RETURN (@nn)
END
GO
函数用法
函数row_count被调用时，需要完整表名称schema.table name作为其输入参数。
例1：
选择dbo.row_count(schema.[table name])，如下面代码所示：
1
use AdventureWorks
2go
3select dbo.row_count ('Sales.SalesOrderDetail')
use AdventureWorks2
go3
select dbo.row_count ('Sales.SalesOrderDetail')在查询分析器中，上述语句返回的结果为12317行，与前面使用count(*)返回的结果一致，但是其执行速度更快，性能更高。
