视图中的“模拟”变量

"Simulate" Variables in a View

提问人：Greg 提问时间：6/29/2023 最后编辑：Greg 更新时间：6/29/2023 访问量：100

问：

背景

再一次，我在一个非常受限的 T-SQL 环境中工作，在这个环境中，人们只能定义 ： 大概是VIEW...

CREATE VIEW My_View AS ...

在引擎盖下。必须（作为 SQL）编写在应用程序 GUI 中其自己的文本字段中，这会带来某些不灵活性，如注释中所述。...

这是我@@VERSION：

Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64)
    Jan 27 2023 16:44:09  
    Copyright (C) 2019 Microsoft Corporation     
    Web Edition (64-bit) on Linux (Amazon Linux 2) <X64>

注意

正文 （...） 仅限于单个 SQL 语句！此外，存储的 s 也无法发挥作用。PROCEDURE

目标

我希望“模拟”“局部”变量的存在，以便可以动态更新某些条件，而不是在整个查询过程中硬编码为文字。具体来说，我希望这些“变量”位于顶部，以便可以轻松调整它们。

问题

遗憾的是，表单的变量在视图中不可用。@x

方法

我假设了这种方法，其“字段”是变量的 CTE。它们的值可以作为以下形式的子查询进行访问：vars(SELECT x FROM vars)

/*********************
** Variable Scalars **
**********************/
WITH vars AS (SELECT
    'Greg'    AS c_name,  -- A client named "Greg".
    GETDATE() AS t_date   -- Transactions occurring today.
    
    
    
/**********
** Query **
**********/
) SELECT
    *
FROM
    Transactions
WHERE
--                     |-------- 'Greg' -------|
    Client_Name      = (SELECT c_name FROM vars) AND
    Transaction_Date = (SELECT t_date FROM vars)
--                     |--------- Today -------|

我进一步建议，可以将值的变量列表指定为进一步的 CTE，通过 STRING_SPLIT（） 派生：

/*********************
** Variable Scalars **
**********************/
WITH vars AS (SELECT
    'Greg' AS c_name
    
    
    
/******************
** Variable List **
*******************/
), vars_dates AS (SELECT DISTINCT CAST([Value] AS DATE) AS vals FROM STRING_SPLIT('
    2023-06-28
    2023-06-01
    2023-01-01
', Char(10)) WHERE [Value] <> ''



/**********
** Query **
**********/
) SELECT
    *
FROM
    Transactions
WHERE
--                      |----------- 'Greg' ----------|
    Client_Name      =  (SELECT c_name FROM vars      ) AND
    Transaction_Date IN (SELECT vals   FROM vars_dates)
--                      |-----------------------------|
--                         {1/1/23, 6/1/23, 6/28/23}

请注意，现在每个值都列在单独的行上，因此用户不必担心标点符号（逗号等）与任何分隔符冲突。vars_dates

问题

到目前为止，我已经能够调用任何需要值的地方。事实上，在我的整个用例中，两者都完美地工作。(SELECT x FROM vars)xvarsvars_dates

但是，是否有任何情况，这些表达式不起作用，而不是文字值和列表？更一般地说，这种方法的局限性是什么？

sql-server 变量 视图 common-table-expression

评论

1赞 Thom A 6/29/2023
我不能代表其他人说话，但你的帖子读起来很像博客，而不是问答帖子;这有点不和谐，因为 Stack Overflow 不是一个博客网站。
1赞 Martin Smith 6/29/2023
SQL Server 应该能够通过此方法使用列统计信息，就像使用了文本一样。例如，这里的估计是准确的 i.stack.imgur.com/30jaG.png
1赞 Stu 6/29/2023
如果解决方案适合您，请使用它！如果有人在 CTE 中添加多行，您也可能会遇到问题。我不确定没有测试它，但我认为优化器无法在编译时使用string_split的内容......？
1赞 Martin Smith 6/29/2023
我也认为，拥有多行子句可能会使事情变得不那么不透明VALUES
1赞 Martin Smith 6/29/2023
我可能会觉得有点乏味，dbfiddle.uk/tBuC9WbA 使用这种方法 - 但取决于视图中其余代码的外观SELECT FROM

答：

1赞 Martin Smith 6/29/2023 #1

您只想在顶部定义一次一些值，然后在整个视图中的重复点中使用它们，以便将来可以在某个位置更改它们。

鉴于您正在使用的约束，您拥有的方法似乎没问题（如果不需要在其他视图中共享这些值）。

它基本上是“伪常量”思想的更局部范围的版本，至少对于标量值，SQL Server 应该能够使用基础文本值进行基数估计（在这方面比实际的 TSQL @variable更好）。

对于标量值，而不是像我这样的子查询，我会介意只使用单行，这样列就可以更容易使用。请在此处查看此示例：SELECT c_name FROM varsCROSS JOINvars

-- An example table, in place of 'Transactions'.
CREATE TABLE #T(X INT);

INSERT INTO #T VALUES (1),(2),(2),(3),(3),(3);


-- The CROSS technique itself.
WITH vars AS 
(
SELECT 3 AS foo
)
SELECT #T.*
FROM vars 
CROSS JOIN #T 
WHERE X = vars.foo


-- Cleanup.
DROP TABLE #T

下面演示了使用该值完成的基数估计，并且它正确地估计了 3 行。3

enter image description here

对于列表，我可能会使用表值构造函数而不是字符串拆分。它应该允许 SQL Server 更轻松地查看列表中的行数（如果它查看这些行中的实际值，可能会对基数估计有其他好处）。

-- ...

/******************
** Variable List **
******************/
), vars_dates AS (SELECT vals FROM (VALUES
    (CAST('2023-06-28' AS date)), /*So the column from the constant scan is typed as date*/
    ('2023-06-01'),
    ('2023-01-01')
) tbl(vals)

-- ...

与实际变量不同，如果您使用非确定性函数，则此方法无法保证值在整个查询过程中保持不变，但您没有任何方法在当前约束下实际保证这一点。

评论

1赞 Greg 6/29/2023
谢谢你，马丁！我已经更新了您的答案，以包含您在评论中（此处）友好地分享的 db<>fiddle 示例。
0赞 Greg 6/29/2023
嗨，马丁！我已经初步更新了您的答案，以包含表值构造函数的有用说明，与您在评论中的建议（此处）非常相似。由于从技术上讲，这不是你的工作——我把临时别名改成了 and——我让你知道，所以我不会“把话放在你嘴里”。tblcol
1赞 Martin Smith 6/29/2023
@Greg - 没关系，我稍微更改了它以从计划中删除计算标量/强制转换，并且只让常量扫描发出正确的数据类型（具有更高的数据类型优先级，因此强制转换其中一个值就足够了）datevarchar
1赞 Greg 6/29/2023
这是表值构造函数的额外好处，这使得它进一步优于： 可以轻松地将动态值插入到列表中，就像 GETDATE（） 一样。STRING_SPLIT()VALUES ((), ('2023-06-01'), ...) tbl(vals)

上一个：将 SQL “Like” 与变量一起使用

下一个：使用变量的 Azure Synapse Analytics SQL 查询