CODEWITHSUNDEEP
sql-server
Saroj Shrestha
Saroj Shrestha

Reputation: 2875

What is the fastest way to calculate total rows in a a huge SQL Server table with conditions

After some surfing on the internet, I have found a way to calculate the total rows in a SQL Server table which is pretty fast. 

SELECT 
    SUM(p.rows) 
FROM 
    sys.partitions AS p
INNER JOIN 
    sys.tables AS t ON p.[object_id] = t.[object_id]
INNER JOIN 
    sys.schemas AS s ON s.[schema_id] = t.[schema_id]
WHERE
    t.name = N'table_name'
    AND s.name = N'dbo'
    AND p.index_id IN (0,1)

However, this code returns the exact total number rows of the table.

But, in my case, I am implementing soft deletes. So, I want to ignore those deleted rows which can be filtered with WHERE deleted_at IS NULL.

That means, deleted rows will have deleted timestamps in deleted_at column.

Is there a way to calculate total rows of data faster than this with some conditions?

Upvotes: 0

Views: 76

Answers (1)

marc_s
marc_s

Reputation: 754298

If you want to take into account some actual data stored in your table, you obviously cannot use the "metadata" approach you've shown.

The probably fastest way to do a count would then be if you have a separate filtered index on a small (e.g. INT) and non-nullable column which excludes the condition you want to check for.

So you can try by adding this filtered index:

CREATE NONCLUSTERED INDEX IX_YourTableName_Active
    ON dbo.YourTableName(deleted_at)
    WHERE deleted_at IS NOT NULL

Update: as @BenThul correctly notices - if you have a filtered index as suggested above, of course you can also check the metadata views for this index and its number of rows - try this:

SELECT 
    SUM(p.rows) 
FROM 
    sys.partitions AS p
INNER JOIN
    sys.indexes i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN 
    sys.tables AS t ON p.[object_id] = t.[object_id]
INNER JOIN 
    sys.schemas AS s ON s.[schema_id] = t.[schema_id]
WHERE
    t.name = N'table_name'
    AND s.name = N'dbo'
    AND i.Name = N'IX_YourTableName_Active'

Upvotes: 2

Related Questions