Get table row count with different methods

get-row-count.sql

/*
Get table row count

See code comments.
See http://blogs.msdn.com/b/martijnh/archive/2010/07/15/sql-server-how-to-quickly-retrieve-accurate-row-count-for-table.aspx


Args:
    @table      NVARCHAR(1000) ,  -- Table name (include DB: 'Northwind..tEmployee').
    @row_count  BIGINT OUTPUT  ,  -- Output result (see example).
    @method     INT = 3        ,  -- Count method:
                                  --     1 — COUNT(1), full table scan. High accuracy.
                                  --     2 — Fast count by index. Not accurate, need indexes.
                                  --     3 — SSMS object property method. Fast, can be not accurate. By default.
                                  --     4 — Table statistics method. Fast and accurate (most often).
    @nolock     BIT = 1           -- Use WITH(NOLOCK) hint (only for method 1).

Raises:
    Procedure fails if:
    — @table does not match 'DB.schema.TableName'.
    — Object @table is not exists.
    — @method out of range [1..4].

Returns:
	@row_count  BIGINT OUTPUT

Examples:
    -- Declare result variable
    DECLARE @row_count INT;
    EXEC tools..prc_getRowCount 
            N'msdb.dbo.sysmail_mailitems', 
            @row_count OUTPUT
    SELECT @row_count;
*/


CREATE PROCEDURE [dbo].[prc_getRowCount] (
    @table      NVARCHAR(1000) ,
    @row_count  BIGINT OUTPUT  ,
    @method     INT = 3        ,
    @nolock     BIT = 1        
)
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @query  NVARCHAR(1000);
    DECLARE @msg    NVARCHAR(1000);

    SET @table = TOOLS.dbo.fnc_regExpReplace('\[|\]', @table, '');
    -- Count table qualification separator
    DECLARE @dot_count   INT = TOOLS.dbo.fnc_countSubstrings(@table, '.');

    -- Verify table name
    IF @dot_count <> 2 BEGIN
        THROW 50001, 'Expected full-qualified table name (like DB.schema.TableName).', 1;
    END;
    IF OBJECT_ID(@table) IS NULL BEGIN
        SET @msg = 'Object ''' + @table + ''' does not exist or permissions did not granted.';
        THROW 50002, @msg, 1;
    END;

    -- Detect DB
    DECLARE @current_db  NVARCHAR(200) = SUBSTRING(@table, 1, CHARINDEX('.', @table, 1) - 1);
    SET @current_db = CASE 
        WHEN @current_db IS NULL OR LTRIM(RTRIM(@current_db)) = '' THEN DB_NAME()
        ELSE @current_db
    END;
    -- Detect Table name
    DECLARE @table_name  NVARCHAR(200) = CASE @dot_count
        WHEN 0 THEN @table
        ELSE RIGHT(@table, CHARINDEX('.', REVERSE(@table)) - 1)
    END;

    -- Performs a full table scan. Slow on large tables.
    IF @method = 1 BEGIN
        SET @query = '
            USE ' + @current_db + ';
            SELECT @row_count = COUNT(1)
            FROM '
            + @table + ' '
            + CASE @nolock 
                    WHEN 1 THEN 'WITH(NOLOCK)'
                    ELSE ''
              END
        ;
    END ELSE

    -- Fast way to retrieve row count. Depends on statistics and is inaccurate.
    -- Run DBCC UPDATEUSAGE(Database) WITH COUNT_ROWS, which can take significant time for large tables.
    IF @method = 2 BEGIN
        SET @query = '
            USE ' + @current_db +';
            SELECT @row_count = CONVERT(bigint, rows)
            FROM sysindexes
            WHERE 
                id = OBJECT_ID(''' + @table + ''')
                AND indid < 2'
        ;        
    END ELSE

    -- The way the SQL management studio counts rows (look at table properties, storage, row count). 
    -- Very fast, but still an approximate number of rows.
    IF @method = 3 BEGIN
        SET @query = '
            USE ' + @current_db + ';
            SELECT @row_count = CAST(p.rows AS float)
            FROM sys.tables AS tbl
            JOIN sys.indexes AS idx 
                ON  idx.object_id = tbl.object_id 
                AND idx.index_id < 2
            JOIN sys.partitions AS p 
                ON p.object_id = CAST(tbl.object_id AS int)
                AND p.index_id = idx.index_id
            WHERE 
                tbl.name = N''' + @table_name + '''
                AND SCHEMA_NAME(tbl.schema_id) = ''dbo'''
        ;
    END ELSE
	
    -- Quick (although not as fast as method 2) operation and equally important, reliable.
    IF @method = 4 BEGIN
        SET @query = '
            USE ' + @current_db + ';
            SELECT @row_count = SUM (row_count)
            FROM sys.dm_db_partition_stats
            WHERE 
                object_id = OBJECT_ID(''' + @table + ''')   
                AND (index_id = 0 or index_id = 1);
        ';

    END ELSE 
        THROW 50000, 'Invalid @method value. Must be in range [1..4].', 1
    ;
    
    EXEC sp_executesql @query, N'@row_count BIGINT OUTPUT', @row_count OUTPUT;
END;