SQL Minimum Row count

Question

I have a stored procedure that allows users to pass in parameters.

SELECT * 
FROM Table1
WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

Answer 1

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *
into mytable
from (select 'X' as c1) x
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)

select * from mytable

--change the 10 to 11 to see it not return
select * from
(
select *, COUNT(*) OVER() CT
from mytable
where c1 = 'X'
) sub
where CT > 10

Answer 2

I am going to offer two ways of doing this, besides the one posted by @scsimon.

The first method is my recommended answer, because it may perform better when presented with a large result set. It effectively implements @AaronBertrand's comment to @nateirvin's (incorrect) answer:

WITH cte AS (
  SELECT *
    ,rownum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
  FROM Table1
  WHERE columnA = @paramA AND @columnB = @paramB
  ----
)
SELECT * 
FROM cte 
WHERE EXISTS (SELECT 1 FROM cte WHERE rownum > 10)

The second method is the old-school procedural form, which has the possible benefit of being easier to maintain, if you are more used to procedural programming languages. Essentially, you create a table variable, populate it with your query results and decide whether or not to emit it based on the number of rows:

BEGIN
  DECLARE @t TABLE(
    columnA INT NULL
   ,columnB INT NULL
   ,columnC NVARCHAR(32) NOT NULL
   --,etc
  );

  INSERT INTO @t
  SELECT * 
  FROM Table1
  WHERE columnA = @paramA AND @columnB = @paramB;

  IF @@ROWCOUNT > 10 SELECT * FROM @t 
  ELSE SELECT TOP(0) * FROM @t; --> optional if you want headers but no rows
END;

I appreciate that measuring statistics isn't always optimal profiling but I found that, for larger datasets, my first method would start to spool results far more quickly, generally finished more quickly overall and performed fewer logical reads, than the other approaches.

Consider, the test case:

DECLARE @n INT = 10000000; --> adjust for simulated workload size

RAISERROR('

-- CTE w/ROWNUM -----------', 10, 1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS IO, TIME ON;
WITH cte AS (
  SELECT TOP(@n) a.*
    ,rownum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
  FROM master..spt_values a, master..spt_values b, master..spt_values c
)
SELECT * 
FROM cte 
WHERE EXISTS (SELECT 1 FROM cte WHERE rownum > 10)
SET STATISTICS IO, TIME OFF;

RAISERROR('

-- SUBQ w/COUNT -----------', 10, 1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS IO, TIME ON;
SELECT * FROM
(
    SELECT *
        ,COUNT(*) OVER() ct
    FROM (SELECT TOP(@n) a.* FROM master..spt_values a, master..spt_values b, master..spt_values c) x
) sub
WHERE ct > 10
SET STATISTICS IO, TIME OFF;

RAISERROR('

-- PROCEDURAL w/TVAR -----------', 10, 1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS IO, TIME ON;
BEGIN
  DECLARE @t TABLE(
    [name] [nvarchar](35) NULL,
    [number] [int] NOT NULL,
    [type] [nchar](3) NOT NULL,
    [low] [int] NULL,
    [high] [int] NULL,
    [status] [int] NULL
  );

  INSERT INTO @t
  SELECT TOP(@n) a.* FROM master..spt_values a, master..spt_values, master..spt_values c;

  IF @@ROWCOUNT > 10 SELECT * FROM @t;
END;
SET STATISTICS IO, TIME OFF;

My statistics (when running on a not-particularly well-spec'd VM) came out as:

-- CTE w/ROWNUM -----------

(10000000 rows affected)
Table 'spt_values'. Scan count 6, logical reads 31, physical reads 6, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 8563 ms,  elapsed time = 19985 ms.


-- SUBQ w/COUNT -----------

(10000000 rows affected)
Table 'Worktable'. Scan count 3, logical reads 30326417, physical reads 90947, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 3, logical reads 31950, physical reads 9, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 67640 ms,  elapsed time = 183607 ms.


-- PROCEDURAL w/TVAR -----------
Table '#6F2063EF'. Scan count 0, logical reads 10045731, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 3, logical reads 31950, physical reads 9, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 43609 ms,  elapsed time = 1035370 ms.

(10000000 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Answer 3

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT * 
FROM 
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber
    FROM Table1
    WHERE columnA =@paramA AND ColumnB=@paramB
) AS subq
WHERE rownumber > 10