August 9, 2014 at 10:44 pm
Hello everyone-
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. Please see my function below and help me understand why this is not working.
CREATE FUNCTION count_rows (@tablename varchar(100)
RETURNS int AS
BEGIN
DECLARE @emp_count AS int
declare @declaration varchar(100)
@declaration='SELECT count(*) FROM ' + @tablename
@emp_count=cast(@declaration as int)
--dbo.TD_EmployeeProfile_FinalV2
RETURN @emp_count
END
GO
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
August 9, 2014 at 11:48 pm
mayankminawat (8/9/2014)
Hello everyone-I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. Please see my function below and help me understand why this is not working.
Firstly the limitations of User Defined Functions prohibit the use of dynamic sql, so this approach has no chance of success.
😎
Here are two alternative ways, the first is a scalar function thatone uses a predefined list of tables.
The second one is an inlinable table value function which queries the sys.partitions system view.
CREATE FUNCTION dbo.FN_COUNT_ROWS
(
@TABLE_NAME NVARCHAR(128)
)
RETURNS INT
AS
BEGIN
DECLARE @RVAL INT = 0;
SELECT @RVAL =
CASE
WHEN @TABLE_NAME = N'dbo.TABLE_1' THEN (SELECT COUNT(*) FROM dbo.TABLE_1)
WHEN @TABLE_NAME = N'dbo.TABLE_2' THEN (SELECT COUNT(*) FROM dbo.TABLE_2)
WHEN @TABLE_NAME = N'dbo.TABLE_3' THEN (SELECT COUNT(*) FROM dbo.TABLE_3)
ELSE 0
END
RETURN @RVAL
END
Usage
DECLARE @TABLE_NAME NVARCHAR(128) = N'dbo_TABLE_2';
SELECT dbo.FN_COUNT_ROWS(@TABLE_NAME)
Method #2
CREATE FUNCTION dbo.FN_COUNT_ROWS2
(
@TABLE_NAME NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
SELECT TOP 1 rows AS ROW_COUNT FROM sys.partitions
WHERE object_id = OBJECT_ID(@TABLE_NAME)
Usage
SELECT ROW_COUNT FROM dbo.FN_COUNT_ROWS2(@TABLE_NAME) AS X
August 10, 2014 at 2:54 am
I'd like to change Eirikurs 2nd method just slightly to cover partitioned tables, too:
CREATE FUNCTION dbo.FN_COUNT_ROWS2
(
@TABLE_NAME NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
SELECT SUM(rows) AS ROW_COUNT FROM sys.partitions
WHERE object_id = OBJECT_ID(@TABLE_NAME)
Also, keep in mind that this result is just an estimate and does not have to match the exact number of rows.
But if you're just looking for a "rough figure" then this is my preferred method -especially for large tables- since it's much faster than a COUNT(*).
August 10, 2014 at 3:23 am
LutzM (8/10/2014)
I'd like to change Eirikurs 2nd method just slightly to cover partitioned tables, too:
CREATE FUNCTION dbo.FN_COUNT_ROWS2(
@TABLE_NAME NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
SELECT SUM(rows) AS ROW_COUNT FROM sys.partitions
WHERE object_id = OBJECT_ID(@TABLE_NAME)
Also, keep in mind that this result is just an estimate and does not have to match the exact number of rows.
But if you're just looking for a "rough figure" then this is my preferred method -especially for large tables- since it's much faster than a COUNT(*).
Thank you Lutz for correcting my incomplete answer;-)
On the "rough figure", if I recall correctly the count should be accurate for SQL Server 2012/2014.
😎
August 10, 2014 at 3:28 am
Quote from BOL(SS2K14):
rows (bigint): Indicates the approximate number of rows in this partition.
August 10, 2014 at 6:49 am
Eirikur Eiriksson (8/10/2014)
LutzM (8/10/2014)
I'd like to change Eirikurs 2nd method just slightly to cover partitioned tables, too:
CREATE FUNCTION dbo.FN_COUNT_ROWS2(
@TABLE_NAME NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
SELECT SUM(rows) AS ROW_COUNT FROM sys.partitions
WHERE object_id = OBJECT_ID(@TABLE_NAME)
Also, keep in mind that this result is just an estimate and does not have to match the exact number of rows.
But if you're just looking for a "rough figure" then this is my preferred method -especially for large tables- since it's much faster than a COUNT(*).
Thank you Lutz for correcting my incomplete answer;-)
On the "rough figure", if I recall correctly the count should be accurate for SQL Server 2012/2014.
😎
And I would like to modify it just a little more:
CREATE FUNCTION dbo.FN_COUNT_ROWS2
(
@TABLE_NAME NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
SELECT
SUM(rows) AS ROW_COUNT
FROM
sys.partitions
WHERE
object_id = OBJECT_ID(@TABLE_NAME) and index_id in (0,1);
August 10, 2014 at 8:20 am
Thank you for all the assistance. I am able to get the function to run but when I do the following in a T-SQL block, I am getting an error:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
set @email = ('XYG@gmail.com')
set @INTFLAGMAX=SELECT ROW_COUNT FROM dbo.FN_COUNT_ROWS2 (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
PRINT @INTFLAGMAX
SET @intFlag = @intFlag + 1
END
GO
-------------------------
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@INTFLAGMAX'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'END'.
August 10, 2014 at 8:47 am
mayankminawat (8/10/2014)
Thank you for all the assistance. I am able to get the function to run but when I do the following in a T-SQL block, I am getting an error:DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
set @email = ('XYG@gmail.com')
set @INTFLAGMAX=SELECT ROW_COUNT FROM dbo.FN_COUNT_ROWS2 (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
PRINT @INTFLAGMAX
SET @intFlag = @intFlag + 1
END
GO
-------------------------
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@INTFLAGMAX'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'END'.
Try this:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
set @email = ('XYG@gmail.com')
SELECT @INTFLAGMAX = ROW_COUNT FROM dbo.FN_COUNT_ROWS2 (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
PRINT @INTFLAGMAX
SET @intFlag = @intFlag + 1
END
GO
August 10, 2014 at 10:14 am
LutzM (8/10/2014)
Quote from BOL(SS2K14):rows (bigint): Indicates the approximate number of rows in this partition.
The value from sys.partitions is accurate, with the exception of a bug in SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2014 at 10:23 am
GilaMonster (8/10/2014)
LutzM (8/10/2014)
Quote from BOL(SS2K14):rows (bigint): Indicates the approximate number of rows in this partition.
The value from sys.partitions is accurate, with the exception of a bug in SQL.
So, BOL is wrong then when showing "approximate number"?
August 10, 2014 at 10:26 am
LutzM (8/10/2014)
GilaMonster (8/10/2014)
LutzM (8/10/2014)
Quote from BOL(SS2K14):rows (bigint): Indicates the approximate number of rows in this partition.
The value from sys.partitions is accurate, with the exception of a bug in SQL.
So, BOL is wrong then when showing "approximate number"?
Less 'wrong' and more 'hasn't been updated since SQL 2000 other than changing sysindexes to sys.partitions'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2014 at 10:32 am
Are the row count values in sys.partitions accurate in general or is there a SQL Server version where the data started being "reliable" rather than "approximate"?
August 10, 2014 at 10:40 am
2005. The row counts in sys.partitions are supposed to be transactionally correct. If they are not, it is a bug and should be reported.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply