 | No support for recursive CTE for computing hierarchical data. |
| No file format to read JSON and XML data from storage. |
| No support for data masking. |
| Data 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. |
| R Services are not ported into SQLDW. |
| No support for Entity Framework. |
| Polybase may fail if an end of line is present within a text field. |
| No 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. |
| Azure SQL DW is only accessible the host name via the Internet. No
endpoints for virtual networks. |
| Row-level security not yet implemented. |
| No support for identities and sequences. However, there is support for
uniqueidentifier and newid(). |
| No support for MERGE statement. |
| No support for Azure Data Lake. |
| No templates dedicated for SQL DW on Visual Studio. |
| No support for replicated tables. |
| No support for USE statement to switch between Azure SQL Data Warehouse
databases. |
| No support for Cursors. Try using WHILE loops as substiture. |
| No support for INSERT ... EXEC |
| Capacity limits are found on
this article. |
| Nesting 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. |
| 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 stored
procedures, encryption option, replication option, table-valued parameters,
read-only parameters, default parameters, execution contexts, return
statement. |
| The Isolation of the transactional support is limited to READ
UNCOMMITTED and this cannot be changed. For more information, please click
here. |
| Transactions are limited in size as mentioned
here. |
| SQL 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.
|
| Azure SQL Data Warehouse does not implement or support
the ERROR_LINE function. For more
information, please click
here. |
| There 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. |
| There 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. |
| Additionally, 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. |
| There 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. |
| Views 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. |
| There 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. |
| SQL 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. |
| When using Polybase to load your tables data to Azure SQL DW, the row
size of the table cannot be greater than 32,767 bytes. |
| Limitations 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. |
| About 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. |
| About 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. |
| Currently, 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. |
![[Company Logo Image]](images/SQLCofee.jpg)
