1

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.

Improve this question
1
  • This is a pretty broad question because they're just different technologies for different purposes, TBH. Kind of like asking "what's the difference between PowerBI and PowerPoint?"
    – J.D.
    May 26, 2022 at 11:46

3 Answers 3

Reset to default
2

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

  1. Unable to use expressions (such as GETDATE() or SUSER_SNAME()) as defaults in tables. Only constants can be used as a default.
  2. Unable to use expressions (such as GETDATE() or SUSER_SNAME()) directly in INSERT statements. Only constants or variables can be used.
  3. No triggers - limiting your options for auditing
  4. No ownership chaining - according to this comment from 2021 this is coming in the future
  5. No cross-database queries - also apparently a future feature
  6. No FOR XML/FOR JSON clauses
  7. No geospatial data types such as geography and geometry
  8. No support for these data types: hierarchyid, image, text, ntext, sql_variant,xml source
  9. No EXECUTE AS, including inside CREATE PROC
  10. No support for cursors (see alternatives)
  11. IDENTITY: Synapse is implemented as 60 shards, refered to as "distributions". Each distribution increments its identity independently. In effect, distribution 1 has IDENTITY(1,60), 2 has IDENTITY (2, 60), and so on. Which value a new row receives will depend on which distribution it lands on. So a later row may have an identity that is less or greater than an earlier row.
  12. There is no means to create an offline .BAK or .BACPAC (with data), so your backup options are limited to the 7 days retention Microsoft provide, or creating copies of your database (which will incur storage costs if paused, or storage & compute costs if running). (You could ETL all the data to a normal database and then make a BAK/BACPAC of that).
  13. Cannot use Table variables - i.e. DECLARE @MyTable TABLE (A_Column int) will fail. You have to use temp tables instead. source
  14. Cannot create UDF's
  15. Cannot start a Procedure from the SSMS GUI, you need to execute code
  16. Cannot INSERT multiple rows using VALUES. e.g.: this won't work: INSERT INTO dbo.MyTable (MyField) VALUES ('ABC'), ('DEF')

Other references:

  • This article goes into differences between Dedicated and Serverless pools in Synapse
Improve this answer
1

That Synapse DB is optimised for throughput rather than latency leads to different application design.

Concurrency slots and workload management require consideration.

Improve this answer
1

  • When creating a table with primary key, SQL server add constraint clause automatically. These DDL code does not compile on Synapse Dedicated SQL, you need to remove the constraint before running the code.

    Effectively:

    CONSTRAINT [PK_DimCustomer] PRIMARY KEY CLUSTERED ([DimCustomerId])

    became:

    PRIMARY KEY NONCLUSTERED NOT ENFORCED,

  • Could not create UDF's

  • You cannot start a Procedure from the SSMS GUI, you need to execute code

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.