Sql-server – Find out beforehand how many records a query has

Unfortunately, the total number of records is not known until the query completes. This means that somehow you need to let the unrestricted query finish, and then grab the subset of rows.

One way to do this is to add COUNT(*) OVER () AS [TotalRows] to the SELECT list. This will return the value as a column with the same value repeated for every row, but still.

SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Of course, that is not simply accessing a value that is freely available. Just compare th execution plan of the query above with the plan for the same thing without the COUNT(*).. expression:

SELECT [object_id], [schema_id], [name], [type_desc]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET 10 ROWS FETCH NEXT 7 ROWS ONLY;

Another method I come up with is described in detail in my answer to a nearly identical question on Stack Overflow:

TSQL: Is there a way to limit the rows returned and count the total that would have been returned without the limit (without adding it to every row)?

The difference between that question and this one is that the other question specifically did not want to return the value as a column in the result set it was counting. Also, the other question was more concerned with the "limit" aspect and this one is concerned with at least "offset" if not both. But adding the "offset" part isn't difficult: just add a loop prior to the loop that returns the desired results to do something like the following:

// assume an input param or variable of: int Offset;

int _RowCounter = 0;
while (Reader.Read() && ++_RowCounter < Offset);

Just for fun, I thought I would experiment with another mechanism that that can run a query and get the rowcount without retrieving the rows: a CURSOR (yes, the E-V-I-L CURSOR). The variable @@CURSOR_ROWS is populated after calling OPEN, even if you never FETCH any rows.

In the following (which I have no idea how well it performs), I make use of FETCH ABSOLUTE x to handle the "Offset" aspect since there is no need to cycle through fetching those initial rows just to not use them. But using ABSOLUTE disallows using options such as FAST_FORWARD and FORWARD_ONLY. Not being able to use FAST_FORWARD, I then used STATIC, but it would have to be tested to see if adding in STATIC actually helped or hurt.

Now, the only way to get the desired results is to store them in a table variable (or temp table would work but I think a table variable is better in this case) so they can be returned as a single result set. Doing FETCH without the INTO clause will return a different result set per call (most likely undesirable).

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE cursed CURSOR LOCAL READ_ONLY STATIC -- can't use FAST_FORWARD or FORWARD_ONLY :(
FOR  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
     FROM   sys.objects so
     ORDER BY so.[object_id] ASC;

DECLARE @object_id INT,
        @schema_id INT,
        @name sysname,
        @type_desc NVARCHAR(60);

DECLARE @RowCounter INT = 0,
        @StartRow INT = (@Offset + 1); -- Offset is how many rows to skip

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL);

OPEN cursed; -- execute the query

SELECT @@CURSOR_ROWS AS [RowCountBeforeRetrievingAnyRows];

FETCH ABSOLUTE @StartRow -- position cursor at beginning of desired range
FROM  cursed
INTO  @object_id, @schema_id, @name, @type_desc;

WHILE (@@FETCH_STATUS = 0 AND @RowCounter < @Limit)
BEGIN
  INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  VALUES (@object_id, @schema_id, @name, @type_desc);

  SET @RowCounter += 1;

  FETCH NEXT
  FROM  cursed
  INTO  @object_id, @schema_id, @name, @type_desc;
END;

CLOSE cursed;
DEALLOCATE cursed;

-- return desired range of rows
SELECT * FROM @Results;


-----------------------------------------------
-- check the actual data to see if it worked
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

Considering that a STATIC CURSOR saves its results into tempdb, the following is essentially the same operationally, but a bit more direct:

DECLARE @Offset INT = 10,
        @Limit INT = 7;

DECLARE @Results TABLE ([object_id] INT NOT NULL, [schema_id] INT NOT NULL,
                        [name] sysname NOT NULL, [type_desc] NVARCHAR(60) NOT NULL,
                        [RowID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY);

INSERT INTO @Results ([object_id], [schema_id], [name], [type_desc])
  SELECT so.[object_id], so.[schema_id], so.name, so.type_desc
  FROM   sys.objects so
  ORDER BY so.[object_id] ASC;

SELECT @@ROWCOUNT AS [TotalRows];

-- return desired range via OFFSET / FETCH (Clustered Index Scan)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
ORDER BY [RowID] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;

-- return desired range via WHERE (Clustered Index Seek)
SELECT [object_id], [schema_id], [name], [type_desc]
FROM @Results
WHERE [RowID] > @Offset
AND   [RowID] <= (@Offset + @Limit)
ORDER BY [RowID] ASC;


-----------------------------------------------
-- check the actual data to see if it worked. OR, maybe this is actually better?
SELECT [object_id], [schema_id], [name], [type_desc], COUNT(*) OVER () AS [TotalRows]
FROM sys.objects
ORDER BY [object_id] ASC OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY;