What are the limitations in Synapse Analytics compared to normal SQL Server?
Marcee last edited by
I'm keen to get a list of the feature limitations or suprises when using Synapse Analytics (a.k.a. Dedicated SQL Pool, used to be known as SQL DW / SQL Data Warehouse) compared to using normal SQL Server. Basically the things that you would expect to be there, but aren't.
I will edit this answer to include new limitations as I find them.
- No triggers - limiting your options for auditing
- No ownership chaining - according to https://docs.microsoft.com/en-us/answers/questions/528220/ideasuggestion-is-it-possible-to-support-ownership.html from 2021 this is coming in the future
- No cross-database queries - also apparently a future feature
- No FOR XML/FOR JSON clauses
- No geospatial data types such as geography and geometry
- No support for these data types: hierarchyid, image, text, ntext, sql_variant,xml https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-data-types
- No EXECUTE AS, including inside CREATE PROC
- No support for cursors ( https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-loops )
- IDENTITY(1,1) will not increment by 1 as you would expect. Instead any unique integer may be assigned when records are inserted to the table.
- https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features goes into differences between Dedicated and Serverless pools in Synapse