在 SQL 数据仓库中使用 T-SQL 循环Using T-SQL loops in SQL Data Warehouse

有关在开发解决方案时在 Azure SQL 数据仓库中使用 T-SQL 循环和替换游标的技巧。Tips for using T-SQL loops and replacing cursors in Azure SQL Data Warehouse for developing solutions.

WHILE 循环的用途Purpose of WHILE loops

SQL 数据仓库支持对重复执行的语句块使用 WHILE 循环。SQL Data Warehouse supports the WHILE loop for repeatedly executing statement blocks. 只要指定的条件都成立,或者在代码专门使用 BREAK 关键字终止循环之前,此 WHILE 循环将一直继续。This WHILE loop continues for as long as the specified conditions are true or until the code specifically terminates the loop using the BREAK keyword. 循环适合用于替换 SQL 代码中定义的游标。Loops are useful for replacing cursors defined in SQL code. 幸运的是,几乎所有以 SQL 代码编写的游标都是快进的只读变体。Fortunately, almost all cursors that are written in SQL code are of the fast forward, read-only variety. 因此,[WHILE] 循环是用于替换游标的绝佳替代方案。Therefore, [WHILE] loops are a great alternative for replacing cursors.

在 SQL 数据仓库中替换游标Replacing cursors in SQL Data Warehouse

但是,在深入学习之前,应该先自问以下问题:“此游标是否可重写以使用基于集的操作?”。However, before diving in head first you should ask yourself the following question: "Could this cursor be rewritten to use set-based operations?." 在许多情况下,答案是肯定的,通常这也是最佳方法。In many cases, the answer is yes and is often the best approach. 基于集的操作的执行速度通常比迭代性的逐行方法要快得多。A set-based operation often performs faster than an iterative, row by row approach.

可以轻松使用循环构造来替换快进只读游标。Fast forward read-only cursors can be easily replaced with a looping construct. 下面是一个简单的示例。The following is a simple example. 此代码示例更新数据库中每个表的统计信息。This code example updates the statistics for every table in the database. 通过迭代循环中的表,每个命令将依次执行。By iterating over the tables in the loop, each command executes in sequence.

首先,创建一个临时表,其中包含用于标识各个语句的唯一行号:First, create a temporary table containing a unique row number used to identify the individual statements:

CREATE TABLE #tbl
WITH
( DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence
,       [name]
,       'UPDATE STATISTICS '+QUOTENAME([name]) AS sql_code
FROM    sys.tables
;

其次,初始化执行循环所需的变量:Second, initialize the variables required to perform the loop:

DECLARE @nbr_statements INT = (SELECT COUNT(*) FROM #tbl)
,       @i INT = 1
;

现在,每次对一个语句执行一次循环:Now loop over statements executing them one at a time:

WHILE   @i <= @nbr_statements
BEGIN
    DECLARE @sql_code NVARCHAR(4000) = (SELECT sql_code FROM #tbl WHERE Sequence = @i);
    EXEC    sp_executesql @sql_code;
    SET     @i +=1;
END

最后,将第一个步骤创建的临时表删除Finally drop the temporary table created in the first step

DROP TABLE #tbl;

后续步骤Next steps

有关更多开发技巧,请参阅 开发概述For more development tips, see development overview.