是否有任何现有的、优雅的、可选的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. 真正的查询又麻烦又大；我希望避免类似于这样的重复： 如果(@max_records为null)开始选择*从{海量查询}开始，否则从{海量查询}开始选择顶部(@max_records)
2. 任意的哨兵值感觉不正确： 从{海量查询}中选择top (ISNULL(@max_records，2147483647)) * 例如，如果@max_records是null，而{massive query}返回的值小于2147483647行，这是否与以下内容相同： 从{海量查询}选择* 或者，从只有50行的表中选择top (2147483647) *是否会受到某种惩罚？

是否有其他现有模式允许在不重复查询或使用哨兵值的情况下进行可选计数限制的结果集？

Answer 1

我正在考虑这个问题，虽然我喜欢IF语句在Problem 1语句中的明确性，但我理解重复的问题。因此，您可以将主查询放在一个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 WHERE @max_records IS NULL
        UNION ALL
        SELECT TOP(ISNULL(@max_records, 0)) * FROM staged WHERE @max_records IS NOT NULL
    ) 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无效，而且它不会编译。

Answer 2

有一些方法，但正如您可能注意到的，所有这些看起来丑陋或不必要的复杂。另外，你真的需要那个订单吗？

您可以使用TOP (100) PERCENT和视图，但是只有当您真的不需要昂贵的ORDER BY ()时，才能使用它，因为Server会忽略您的(如果您尝试了.)。

我建议利用存储过程，但首先让我们解释不同类型的proc：

硬编码参数嗅探

--Note the lack of a real parametrized column. See notes below.
IF OBJECT_ID('[dbo].[USP_TopQuery]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery AS ')
GO
ALTER PROC [dbo].[USP_TopQuery] @MaxRows NVARCHAR(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) = N'SELECT * FROM dbo.ThisFile'
      , @Option NVARCHAR(50) = 'TOP (' + @MaxRows + ') *'
IF ISNUMERIC(@MaxRows) = 0
    EXEC sp_executesql @SQL     
ELSE
    BEGIN
        SET @SQL = REPLACE(@SQL, '*', @Option)
        EXEC sp_executesql @SQL
    END
END

局部变量参数嗅探

IF OBJECT_ID('[dbo].[USP_TopQuery2]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery2 AS ')
GO
ALTER PROC [dbo].[USP_TopQuery2] @MaxRows INT NULL
AS
BEGIN
DECLARE @Rows INT;
    SET @Rows = @MaxRows;

IF @MaxRows IS NULL
    SELECT *
    FROM dbo.THisFile   
ELSE
    SELECT TOP (@Rows) *
    FROM dbo.THisFile
END

无参数嗅探，旧方法

IF OBJECT_ID('[dbo].[USP_TopQuery3]', 'U') IS NULL
    EXECUTE('CREATE PROC dbo.USP_TopQuery3 AS ')
GO
ALTER PROC [dbo].[USP_TopQuery3] @MaxRows INT NULL
AS
BEGIN

IF @MaxRows IS NULL
    SELECT *
    FROM dbo.THisFile   
ELSE
    SELECT TOP (@MaxRows) *
    FROM dbo.THisFile
END

请注意参数嗅探： Server在编译时(而不是在解析时)初始化存储过程中的变量。 这意味着Server将无法猜测查询，并将为查询选择最后有效的执行计划，而不管它是否良好。

有两种方法，硬编码局部变量，允许优化器猜测。

1. 参数嗅探的硬编码

- Use sp\_executesql to not only reuse the query, but prevent SQL Injection. 
- However, in this type of query, will not always perform substantially better since a TOP Operator is not a column or table (so the statement effectively has no variables **in this version I used**)
- Statistics at the time of the creation of your compiled plan will dictate how affective the method is if you are not using a variable on a predicate (`ON`, `WHERE`, `HAVING`)
- Can use options or hint to `RECOMPILE` to overcome this issue.

​

1. 变参数嗅探

- Variable Paramter sniffing, on the other hand, is flexible enough to work witht the statistics here, and in my own testing it seemed the variable parameter had the advantage of the query using statistics (particularly after I updated the statistics).

​

​

​

• 最终，性能问题在于哪种方法将使用最少的步骤遍历传单。Statistics，表中的行，以及规则，用于Server何时决定使用扫描而非查找会影响性能。
• 运行不同的值将显示性能显著变化，尽管通常比USP_TopQuery3更好。因此，并不认为一种方法比另一种方法更好。

- Also note you can use a table-valued function to do the same, but as Dave Pinal would say:

​

如果你要回答‘为了避免重复代码，你使用函数’-请仔细考虑！存储过程也可以这样做..。 如果你要回答‘函数可以在选择中使用，而存储过程不能使用’-再仔细思考！ SQL SERVER -向您提问何时使用函数和何时使用存储过程

Answer 3

你可以用SET ROWCOUNT

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

  IF @max_records IS NOT NULL
  BEGIN
     SET ROWCOUNT @max_records
  END

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

  SET ROWCOUNT 0

end