A Quicker Alternative to SELECT COUNT(*)
Right now I am working on a process to transfer a single table between databases. There are over 36 million rows in the source table, and as a result I am batching the inserts in a loop. I need the upper limit of my loop, which is equal to the number of rows in the source table. Usually, I would set the @rowlimit variable using a line of code similar to this:
SELECT @rowlimit = COUNT(*) FROM sourcetable
In the case of the 36 million rows, the SELECT COUNT(*) was taking far too long. Fortunately, there is an alternative in the sp_spaceused system stored procedure. Where the SELECT COUNT(*) operation was taking up to 20 minutes, the following code returns in seconds.
USE databasename GO CREATE TABLE #spaceused (name NVARCHAR(128) ,rows CHAR(11) ,reserved VARCHAR(18) ,data VARCHAR(18) ,index_size VARCHAR(18) ,unused VARCHAR(18)) DECLARE @rowlimit BIGINT INSERT INTO #spaceused EXEC sp_spaceused tablename SELECT @rowlimit = rows FROM #spaceused WHERE name = 'tablename' SELECT @rowlimit DROP TABLE #spaceused
I hope you find this useful.