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



  • 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.

    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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2