Unable to create table in Synapse Analytics with SMALLINT column datatype with SQL Pool

Question

I am trying to create a table in Synapse Analytics using SQL Pools, based on the flight times dataset, but keep getting the following error:

Parse error at line: 4, column: 25: Incorrect syntax near '[SMALLINT]'.

I don't face this issue in serverless, and using other non-numeric types also works fine.

The create table DDL is as follows:

CREATE TABLE [flightdata_csv] (
    [FL_DATE] [DATE],
    [OP_CARRIER] [VARCHAR](2),
    [OP_CARRIER_FL_NUM] [SMALLINT] CHECK ([OP_CARRIER_FL_NUM] > 0),
    [ORIGIN] [VARCHAR](3),
    [ORIGIN_CITY_NAME] [VARCHAR](64),
    [DEST] [VARCHAR](3),
    [DEST_CITY_NAME] [VARCHAR](64),
    [CRS_DEP_TIME] [SMALLINT] CHECK ([CRS_DEP_TIME] >= 0 AND [CRS_DEP_TIME] <= 2359),
    [DEP_TIME] [SMALLINT] CHECK ([DEP_TIME] >= 0 AND [DEP_TIME] <= 2359),
    [WHEELS_ON] [SMALLINT] CHECK ([WHEELS_ON] >= 0 AND [WHEELS_ON] <= 2359),
    [TAXI_IN] [SMALLINT] CHECK ([TAXI_IN] >= 0),
    [CRS_ARR_TIME] [SMALLINT] CHECK ([CRS_ARR_TIME] >= 0 AND [CRS_ARR_TIME] <= 2359),
    [ARR_TIME] [SMALLINT] CHECK ([ARR_TIME] >= 0 AND [ARR_TIME] <= 2359),
    [CANCELLED] [TINYINT] CHECK (CANCELLED < 2),
    [DISTANCE] [SMALLINT] CHECK (DISTANCE > 0)
);

The full stack trace is as follows:

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[103010:1]: Parse error at line: 4, column: 25: Incorrect syntax near '[SMALLINT]'. --->
Microsoft.SqlServer.DataWarehouse.Sql.Parser.ParseErrorException: Parse error at line: 4, column: 25: Incorrect syntax near '[SMALLINT]'. ---> antlr.NoViableAltException: unexpected token:
Microsoft.SqlServer.DataWarehouse.Sql.Parser.TSqlWhitespaceTokenFilter+TSqlParserTokenProxyWithIndex at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.regularColumnBody(IndexAffectingStatement statementType, ColumnDefinition vParent) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.columnDefinition(IndexAffectingStatement statementType, TableDefinition vParent) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.tableElement(IndexAffectingStatement statementType, TableDefinition vParent) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.tableElementList(CreateTableStatement vParent) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.nonCTASCreateTableStatement(CreateTableStatement vParent) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.createTableStatement() at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.statement() at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.batch() at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.DSql10ParserInternal.script() at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.TSql80ParserBaseInternal.ParseRuleWithStandardExceptionHandling[T] --- End of inner exception stack trace --- at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.TSql80ParserBaseInternal.ParseRuleWithStandardExceptionHandling[T] at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.TSql.Parse(String input, SqlVersion sqlVersion, Boolean initialQuotedIdentifiers, StatementParsingContext statementParsingContext) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.Factory.DSqlParserFacade.Parse(String sql, StatementParsingContext statementParsingContext) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.Factory.DSqlParserFacade.<>c__DisplayClass3_0.<ParseStatement>b__0() at
Microsoft.SqlServer.DataWarehouse.Sql.Utilities.QPTelemetryInfo.ExecuteAndTime(Action callback, TimerType timerType) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.Factory.DSqlParserFacade.ParseStatement(String sql, ISchema schema, StatementParsingContext statementParsingContext, StatementExecutionContext statementExecutionContext, Boolean forPrepare, IQPTelemetry queryProcessingTelemetry) --- End of inner exception stack trace --- at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.Factory.DSqlParserFacade.ParseStatement(String sql, ISchema schema, StatementParsingContext statementParsingContext, StatementExecutionContext statementExecutionContext, Boolean forPrepare, IQPTelemetry queryProcessingTelemetry) at
Microsoft.SqlServer.DataWarehouse.Sql.Parser.Factory.SQLStatementFactory.CreateStatement(String sqlQuery, ISchema schema, ICancelableRootObject cancelableRoot, StatementExecutionContext statementExecutionContext, Boolean forPrepare, IQPTelemetry queryProcessingTelemetry, SqlFrontEndRequest request) at
Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.CreateStatement(String query, StatementExecutionContext statementExecutionContext, Boolean forPrepare, SqlFrontEndRequest request, IQPTelemetry queryProcessingTelemetry) at
Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.<>c__DisplayClass81_0.<GenerateValidStatement>b__0() at
Microsoft.SqlServer.DataWarehouse.Sql.Utilities.QPTelemetryInfo.ExecuteAndTime(Action callback, TimerType timerType) at
Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.GenerateValidStatement(IQueryContext queryCtx, String query, StatementExecutionContext statementExe

Answer 1

I created a dedicated pool and ran your CREATE TABLE statement and got exactly the same error as you did. Since you ran from Synapse Studio, the surrounding of the message was a different from what I'm used to.

Anyway, I looked at the topic for CREATE TABLE for Azure Synapse Analytics, and it's clear that it does not support CHECK constraints. This is a little confusing with Microsoft's various SQL products which on first glance seems to have the same language T-SQL, but the feature parity is far from 100 %. And even if the serverless and dedicated pools both are under the Synapse umbrella, they still come from different origins.

In this particular case, it would certainly have been easier for you to find the issue, if the error message had flagged CHECK and not SMALLINT. Although best practice is to name your constraints explicly, and if I try:

CREATE TABLE [flightdata_csv] (
    [FL_DATE] [DATE],
    [OP_CARRIER] [VARCHAR](2),
    [OP_CARRIER_FL_NUM] [SMALLINT] CONSTRAINT ck_flightdata_op_carrier CHECK ([OP_CARRIER_FL_NUM] > 0),
    [ORIGIN] [VARCHAR](3),
    [ORIGIN_CITY_NAME] [VARCHAR](64),
    [DEST] [VARCHAR](3),
    [DEST_CITY_NAME] [VARCHAR](64),
    [CRS_DEP_TIME] [SMALLINT] CHECK ([CRS_DEP_TIME] >= 0 AND [CRS_DEP_TIME] <= 2359),
    [DEP_TIME] [SMALLINT] CHECK ([DEP_TIME] >= 0 AND [DEP_TIME] <= 2359),
    [WHEELS_ON] [SMALLINT] CHECK ([WHEELS_ON] >= 0 AND [WHEELS_ON] <= 2359),
    [TAXI_IN] [SMALLINT] CHECK ([TAXI_IN] >= 0),
    [CRS_ARR_TIME] [SMALLINT] CHECK ([CRS_ARR_TIME] >= 0 AND [CRS_ARR_TIME] <= 2359),
    [ARR_TIME] [SMALLINT] CHECK ([ARR_TIME] >= 0 AND [ARR_TIME] <= 2359),
    [CANCELLED] [TINYINT] CHECK (CANCELLED < 2),
    [DISTANCE] [SMALLINT] CHECK (DISTANCE > 0)
);

I get:

Msg 103010, Level 16, State 1, Line 1 Parse error at line: 4, column: 72: Incorrect syntax near 'CHECK'.

Answer 2

Exactly how are you running this statement? Judging from the stack trace, this seems to occur in a client somewhere. Could that by Synapse Studio?

Try running the CREATE TABLE from SSMS instead.

(I don't have a dedicated pool set up right now, so I can't make a quick test myself.