Migrate your SQL code to SQL Data Warehouse

This article explains code changes you will probably need to make when migrating your code from another database to SQL Data Warehouse. Some SQL Data Warehouse features can significantly improve performance as they are designed to work in a distributed fashion. However, to maintain performance and scale, some features are also not available.

Common T-SQL Limitations

The following list summarizes the most common features that SQL Data Warehouse does not support. The links take you to workarounds for the unsupported features:

Fortunately most of these limitations can be worked around. Explanations are provided in the relevant development articles referenced above.

Supported CTE features

Common table expressions (CTEs) are partially supported in SQL Data Warehouse. The following CTE features are currently supported:

  • A CTE can be specified in a SELECT statement.
  • A CTE can be specified in a CREATE VIEW statement.
  • A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.
  • A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.
  • A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.
  • A remote table can be referenced from a CTE.
  • An external table can be referenced from a CTE.
  • Multiple CTE query definitions can be defined in a CTE.

CTE Limitations

Common table expressions have some limitations in SQL Data Warehouse including:

  • A CTE must be followed by a single SELECT statement. INSERT, UPDATE, DELETE, and MERGE statements are not supported.
  • A common table expression that includes references to itself (a recursive common table expression) is not supported (see below section).
  • Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
  • An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.
  • When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
  • When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL Server and other PDW statements because of the way binding is implemented for sp_prepare. If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

Recursive CTEs

Recursive CTEs are not supported in SQL Data Warehouse. The migration of recursive CTE can be somewhat complex and the best process is to break it into multiple steps. You can typically use a loop and populate a temporary table as you iterate over the recursive interim queries. Once the temporary table is populated you can then return the data as a single result set. A similar approach has been used to solve GROUP BY WITH CUBE in the group by clause with rollup / cube / grouping sets options article.

Unsupported system functions

There are also some system functions that are not supported. Some of the main ones you might typically find used in data warehousing are:

  • @@IDENTITY()
  • @@ROWCOUNT()

Some of these issues can be worked around.

@@ROWCOUNT workaround

To work around lack of support for @@ROWCOUNT, create a stored procedure that will retrieve the last row count from sys.dm_pdw_request_steps and then execute EXEC LastRowCount after a DML statement.

WITH LastRequest as
(   SELECT TOP 1    request_id
    FROM            sys.dm_pdw_exec_requests
    WHERE           session_id = SESSION_ID()
    AND             resource_class IS NOT NULL
    ORDER BY end_time DESC
LastRequestRowCounts as
    SELECT  step_index, row_count
    FROM    sys.dm_pdw_request_steps
    WHERE   row_count >= 0
    AND     request_id IN (SELECT request_id from LastRequest)
SELECT TOP 1 row_count FROM LastRequestRowCounts ORDER BY step_index DESC

Next steps

For a complete list of all supported T-SQL statements, see Transact-SQL topics.