[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Azure SQL Data Warehouse Limitations

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

Azure SQL Data Warehouse Limitations


Date created: 12/14/2016.

Last updated: 12/14/2016.

 

The following are some current limitations Azure SQL Data Warehouse and changes of behavior of instructions/features on Azure SQL Data Warehouse compared with SQL Server:

 

bulletNo support for recursive CTE for computing hierarchical data.
bulletNo file format to read JSON and XML data from storage.
bulletNo support for data masking.
bulletData types not supported are: geography, geometry, hierarchyid, image, text, ntext, sql_variant, timestamp, and xml. No support for spatial data types. No support for BLOB data types.
bulletR Services are not ported into SQLDW.
bulletNo support for Entity Framework.
bulletPolybase may fail if an end of line is present within a text field.
bulletNo support for elastic queries or cross database queries on SQL DW. SQL Data Warehouse runs the entire data warehouse workload within one database. Cross database joins are not permitted. Therefore SQL Data Warehouse expects all tables used by the warehouse to be stored within the one database. For more information, please click here.
bulletAzure SQL DW is only accessible the host name via the Internet. No endpoints for virtual networks.
bulletRow-level security not yet implemented.
bulletNo support for identities and sequences. However, there is support for uniqueidentifier and newid().
bulletNo support for MERGE statement.
bulletNo support for Azure Data Lake.
bulletNo templates dedicated for SQL DW on Visual Studio.
bulletNo support for replicated tables.
bulletNo support for USE statement to switch between Azure SQL Data Warehouse databases.
bulletNo support for Cursors. Try using WHILE loops as substiture.
bulletNo support for INSERT ... EXEC
bulletCapacity limits are found on this article.
bulletNesting stored procedures is limited to 8 levels on Azure SQLDW, in contrast to SQL Server that is limited to 32 levels. For more information, please click here.
bulletThere 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 stored procedures, encryption option, replication option, table-valued parameters, read-only parameters, default parameters, execution contexts, return statement.
bulletThe Isolation of the transactional support is limited to READ UNCOMMITTED and this cannot be changed. For more information, please click here.
bulletTransactions are limited in size as mentioned here.
bulletSQL Data Warehouse uses the XACT_STATE function to report a failed transaction using the value -2. This means that the transaction has failed and is marked for rollback only. SQL Server uses the value -1. For more information, please click here.
bulletAzure SQL Data Warehouse does not implement or support the ERROR_LINE function. For more information, please click here.
bulletThere are some changes on the behavior of THROW and RAISERROR. User defined error messages numbers cannot be in the 100,000 - 150,000 range for THROW. RAISERROR error messages are fixed at 50,000. Use of sys.messages is not supported. For more information, please click here.
bulletThere few more transactions limitations on SQL Azure Data Warehouse. No distributed transactions. No nested transactions permitted. No save points allowed. No named transactions. No marked transactions. No support for DDL such as CREATE TABLE inside a user defined transaction.
bulletAdditionally, when you pause or scale your SQL Data Warehouse any in-flight transactions are terminated immediately, causing any open transactions to be rolled back. For more information, please click here.
bulletThere are limitations assigning variables on Azure SQL Data Warehouse. You cannot initialize and use a variable in the same DECLARE statement. You cannot use SELECT or UPDATE for variable assignment, you are limited to use DECLARE and SET. For more information, please click here.
bulletViews in SQL Data Warehouse are metadata only. Consequently the following options are not available: there is no schema binding option, base tables cannot be updated through the view, views cannot be created over temporary tables, there is no support for the EXPAND / NOEXPAND hints, and there are no indexed views in SQL Data Warehouse. For more information about restrictions and changes in behavior of views on Azure SQL Data Warehouse, please click here.
bulletThere are some limitations on the GROUP BY clause. The following options are not supported: GROUP BY with ROLLUP, GROUPING SETS, GROUP BY with CUBE. UNION ALL should be used on these cases.
bulletSQL Data Warehouse does not support ANSI joins in the FROM clause of an UPDATE statement or DELETE statement. Use Create Table As Select (CTAS) instead. For more information, please click here.
bulletWhen using Polybase to load your tables data to Azure SQL DW, the row size of the table cannot be greater than 32,767 bytes.
bulletLimitations with Columnstore tables. Columnstore tables do not support secondary non-clustered indexes. Consider heap or clustered index tables instead. Columnstore tables do not support varchar(max), nvarchar(max) and varbinary(max). Consider heap or clustered index instead. For more information, please click here.
bulletAbout partitioning tables, Azure SQL Data Warehouse supports one partition column per table (same as SQL Server). There are some syntax difference compared with SQL Server as explained here.
bulletAbout statistics, Azure SQL Data Warehouse does not have a system stored procedure equivalent to sp_create_stats in SQL Server and DBCC SHOW_STATISTICS() is more strictly implemented in SQL Data Warehouse compared to SQL Server. For more information, visit this article.
bulletCurrently, only session scoped temporary tables are supported. Global Temporary Tables are not supported. In addition, views cannot be created on temporary tables. In SQL Data Warehouse temporary tables exist at the session level, they are only visible to the session in which they were created and are automatically dropped when that session logs off. Temporary tables offer a performance benefit because their results are written to local rather than remote storage. Temporary tables are slightly different in Azure SQL Data Warehouse than Azure SQL Database as they can be accessed from anywhere inside the session, including both inside and outside of a stored procedure. For more information, visit this article.

 

 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.