使用 SQL 数据仓库中的存储过程Using stored procedures in SQL Data Warehouse

有关在开发解决方案时实现 Azure SQL 数据仓库中的存储过程的技巧。Tips for implementing stored procedures in Azure SQL Data Warehouse for developing solutions.

期望What to expect

SQL 数据仓库支持 SQL Server 中使用的许多 T-SQL 功能。SQL Data Warehouse supports many of the T-SQL features that are used in SQL Server. 更重要的是,可使用特定的横向扩展功能将解决方案的性能最大化。More importantly, there are scale-out specific features that you can use to maximize the performance of your solution.

但是,为了保持 SQL 数据仓库的缩放性和性能,还有一些具有行为差异的功能以及其他不支持的功能。However, to maintain the scale and performance of SQL Data Warehouse there are also some features and functionality that have behavioral differences and others that are not supported.

存储过程简介Introducing stored procedures

存储过程很适合用于封装 SQL 代码;将它存储在数据仓库中数据附近。Stored procedures are a great way for encapsulating your SQL code; storing it close to your data in the data warehouse. 通过将代码封装成可管理的单位,促使代码有更大的可重复使用性,存储过程帮助开发人员将其解决方案模块化。Stored procedures help developers modularize their solutions by encapsulating the code into manageable units; facilitating greater reusability of code. 每个存储过程还可接受参数,使其更具弹性。Each stored procedure can also accept parameters to make them even more flexible.

SQL 数据仓库提供简化且流畅的存储过程实现。SQL Data Warehouse provides a simplified and streamlined stored procedure implementation. 相比于 SQL Server,最大差异是存储过程不是预先编译的代码。The biggest difference compared to SQL Server is that the stored procedure is not pre-compiled code. 在数据仓库中,与针对大型数据卷运行查询所用的时间相比,编译时间非常少。In data warehouses, the compilation time is small in comparison to the time it takes to run queries against large data volumes. 保证存储过程代码针对大量查询正确优化更为重要。It is more important to ensure the stored procedure code is correctly optimized for large queries. 目标是要节省时数、分钟数和秒数,而不是毫秒数。The goal is to save hours, minutes, and seconds, not milliseconds. 因此,将存储过程视为 SQL 逻辑的容器更有帮助。It is therefore more helpful to think of stored procedures as containers for SQL logic.

SQL 数据仓库执行存储过程时,SQL 语句在运行时进行解析、转换和优化。When SQL Data Warehouse executes your stored procedure, the SQL statements are parsed, translated, and optimized at run time. 在此过程中,每个语句都转换为分布式查询。During this process, each statement is converted into distributed queries. 针对数据执行的 SQL 代码与提交的查询不同。The SQL code that is executed against the data is different than the query submitted.

嵌套存储过程Nesting stored procedures

如果存储过程调用其他存储过程或执行动态 SQL,则将内部存储过程或代码调用视为嵌套。When stored procedures call other stored procedures, or execute dynamic SQL, then the inner stored procedure or code invocation is said to be nested.

SQL 数据仓库最多支持 8 个嵌套级别。SQL Data Warehouse supports a maximum of eight nesting levels. 这与 SQL Server 稍有不同。This is slightly different to SQL Server. SQL Server 中的嵌套级别为 32。The nest level in SQL Server is 32.

最上层存储过程调用等同于嵌套级别 1。The top-level stored procedure call equates to nest level 1.

EXEC prc_nesting

如果存储过程还调用另一个 EXEC,则嵌套级别将增加到 2。If the stored procedure also makes another EXEC call, the nest level increases to two.

CREATE PROCEDURE prc_nesting
AS
EXEC prc_nesting_2  -- This call is nest level 2
GO
EXEC prc_nesting

如果第二个过程随后执行某种动态 SQL,则嵌套级别将增加到 3。If the second procedure then executes some dynamic SQL, the nest level increases to three.

CREATE PROCEDURE prc_nesting_2
AS
EXEC sp_executesql 'SELECT 'another nest level'  -- This call is nest level 2
GO
EXEC prc_nesting

请注意,SQL 数据仓库当前不支持 @@NESTLEVELNote, SQL Data Warehouse does not currently support @@NESTLEVEL. 需要跟踪嵌套级别。You need to track the nest level. 不太可能超过 8 个嵌套级别的限制,但如果超过了,则需要重新修改代码,以使它符合限制内的嵌套级别。It is unlikely for you to exceed the eight nest level limit, but if you do, you need to rework your code to fit the nesting levels within this limit.

INSERT..EXECUTEINSERT..EXECUTE

SQL 数据仓库不允许通过 INSERT 语句使用存储过程的结果集。SQL Data Warehouse does not permit you to consume the result set of a stored procedure with an INSERT statement. 但是,可以使用替代方法。However, there is an alternative approach you can use. 有关示例,请参阅临时表上的文章。For an example, see the article on temporary tables.

限制Limitations

SQL 数据仓库中未实现 Transact-SQL 存储过程的某些方面。There are some aspects of Transact-SQL stored procedures that are not implemented in SQL Data Warehouse.

它们具有以下特点:They are:

  • 临时存储过程temporary stored procedures
  • 编号的存储过程numbered stored procedures
  • 扩展的存储过程extended stored procedures
  • CLR 存储过程CLR stored procedures
  • 加密选项encryption option
  • 复制选项replication option
  • 表值参数table-valued parameters
  • 只读参数read-only parameters
  • 默认参数default parameters
  • 执行上下文execution contexts
  • return 语句return statement

后续步骤Next steps

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