Syntax differences between Synapse and SQL Server 2019

Question

I need to script DDL of 100 tables from Oracle to Synapse. I am  SQL Server 2019 DBA/dev and have this new project for Synapse target. I considering on using SSMA for Oracle since Synapse is supported.

Wondering what T-SQL syntax differences I should be aware for this migration? 

Answer 1

Hello @HSever ,

Welcome to the MS Q&A platform.

Alter, Drop, and Truncate syntax is the same between Synapse and SQL 2019. But for Create, the optional values are a little bit different between Synapse and SQL

Ex: Synapse supports the DISTRIBUTION method, but SQL won't support this

Synapse:
CREATE TABLE [dbo].[ADF]
(
[ID] [int] NULL,
[Date] [date] NULL,
[col1] [int] NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN

)
GO

SQL

CREATE TABLE [dbo].ADF_new ON [PRIMARY]
GO

Create table syntax for SQL:
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16

Create table syntax for Synapse:
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7

Alter, Drop and Truncate syntax is the same for both Synapse and SQL 2019
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver16

Please see the below screenshots for your reference.

Please note: If there are any syntax differences, they will be called out in the documentation links.

Ex: 1) Serverless SQL pool in Azure Synapse Analytics supports only external and temporary tables.

Ex: 2) In Azure Synapse Analytics and Analytics Platform System (PDW):

TRUNCATE TABLE is not allowed within the EXPLAIN statement.

TRUNCATE TABLE cannot be ran inside of a transaction.

I hope this helps. Please let me know if you have any further questions.

------------------------------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators