Stored procedures in SQL Data Warehouse
SQL Data Warehouse supports many of the Transact-SQL features found in SQL Server. More importantly there are scale out specific features that we will want to leverage to maximize the performance of your solution.
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.
This article explains how to implement stored procedures within SQL Data Warehouse.
Introducing stored procedures
Stored procedures are a great way for encapsulating your SQL code; storing it close to your data in the data warehouse. By encapsulating the code into manageable units stored procedures help developers modularize their solutions; facilitating greater re-usability of code. Each stored procedure can also accept parameters to make them even more flexible.
SQL Data Warehouse provides a simplified and streamlined stored procedure implementation. The biggest difference compared to SQL Server is that the stored procedure is not pre-compiled code. In data warehouses we are generally less concerned with the compilation time. It is more important that the stored procedure code is correctly optimised when operating against large data volumes. The goal is to save hours, minutes and seconds not milliseconds. It is therefore more helpful to think of stored procedures as containers for SQL logic.
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. The SQL code that is actually executed against the data is different to the query submitted.
Nesting stored procedures
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 Data Warehouse support a maximum of 8 nesting levels. This is slightly different to SQL Server. The nest level in SQL Server is 32.
The top level stored procedure call equates to nest level 1
If the stored procedure also makes another EXEC call then this will increase the nest level to 2
CREATE PROCEDURE prc_nesting AS EXEC prc_nesting_2 -- This call is nest level 2 GO EXEC prc_nesting
If the second procedure then executes some dynamic sql then this will increase the nest level to 3
CREATE PROCEDURE prc_nesting_2 AS EXEC sp_executesql 'SELECT 'another nest level' -- This call is nest level 2 GO EXEC prc_nesting
Note SQL Data Warehouse does not currently support @@NESTLEVEL. You will need to keep a track of your nest level yourself. It is unlikely you will hit the 8 nest level limit but if you do you will need to re-work your code and "flatten" it so that it fits within this limit.
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.
Please refer to the following article on temporary tables for an example on how to do this.
There are some aspects of Transact-SQL stored procedures that are not implemented in SQL Data Warehouse.
- temporary stored procedures
- numbered stored procedures
- extended stored procedures
- CLR stored procedures
- encryption option
- replication option
- table-valued parameters
- read-only parameters
- default parameters
- execution contexts
- return statement
For more development tips, see development overview.