sql - 可选 TOP 子句是否有任何现有的、优雅的模式？

Question

采用此处定义的(简化的)存储过程:

create procedure get_some_stuffs
  @max_records int = null
as
begin
  set NOCOUNT on

  select top (@max_records) *
  from my_table
  order by mothers_maiden_name
end

仅当提供了 @max_records 时，我才想限制所选记录的数量。

问题:

1. 真实查询是令人讨厌且庞大的；我想避免类似这样的重复:

   if(@max_records is null)
   begin
     select *
     from {massive query}
   end
   else
   begin
     select top (@max_records)
     from {massive query}
   end
   

2. 任意哨兵值感觉不对:

   select top (ISNULL(@max_records, 2147483647)) *
   from {massive query}
   

   例如，如果 @max_records 为 null 并且 {massive query} 返回的行数少于 2147483647 行，这是否与以下内容相同:

   select * 
   from {massive query}
   

   或者从只有 50 行的表格中选择 top (2147483647) * 是否会受到某种惩罚？

是否有任何其他现有模式允许在不重复查询或使用标记值的情况下实现可选的计数限制结果集？

Answer 1

我正在考虑这个问题，虽然我喜欢 Problem 1 语句中 IF 语句的明确性，但我理解重复的问题。因此，您可以将主查询放在单个 CTE 中，并使用一些技巧从中查询(粗体部分是此解决方案的亮点):

CREATE PROC get_some_stuffs
(
    @max_records int = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH staged AS (
        -- Only write the main query one time
        SELECT * FROM {massive query}
    )
    -- This part below the main query never changes:
    SELECT * 
    FROM (
        -- A little switcheroo based on the value of @max_records
        SELECT * FROM staged <strong>WHERE @max_records IS NULL</strong>
        <strong>UNION ALL</strong>
        SELECT <strong>TOP(ISNULL(@max_records, 0))</strong> * FROM staged <strong>WHERE @max_records IS NOT NULL</strong>
    ) final
    -- Can't use ORDER BY in combination with a UNION, so move it out here
    ORDER BY mothers_maiden_name
END

我查看了每个查询的实际查询计划，优化器足够智能，可以完全避免 UNION ALL 中不需要运行的部分。

ISNULL(@max_records, 0) 位于其中，因为 TOP NULL 无效，并且无法编译。