What are the limitations in Synapse Analytics compared to normal SQL Server?

[Marcee]

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.

[meriah]

I will edit this answer to include new limitations as I find them.

1. No triggers - limiting your options for auditing
2. 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
3. No cross-database queries - also apparently a future feature
4. No FOR XML/FOR JSON clauses
5. No geospatial data types such as geography and geometry
6. 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
7. No EXECUTE AS, including inside CREATE PROC
8. No support for cursors ( https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-loops )
9. 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.

Other references:

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features goes into differences between Dedicated and Serverless pools in Synapse