Create Table issues

[tanthiamhuat]

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps...

I have tried for hours, and do not see why it cannot go through the code below:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

whether it is in Databricks or in Azure Synapse SQL, it says the same error:

[#2 · Accepted solution]

Based on the code and the screenshot you shared I think you're trying to run SQL code specific for Azure Synapse dedicated SQL pool (which supports columnstore indexes) using a notebook and Spark pool, which is not the same as dedicated SQL pool (please note that different engines typically do not operate the same SQL dialect).

For your code to run successfully you should first create a dedicated SQL pool, and then run the code using SQL Script in the Synapse Studio. See this tutorial for the reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/get-started-analyze-sql-pool.

HTH,
Pawel

[#3]

@pawelpotasinski 

yes, exactly, thank you very much. We need to create that SQL pool before we can run the SQL script. Microsoft should give us a much better error message than saying syntax error of extra "(", which makes it so confusing....