Parse error on T-SQL WITH CLUSTERED statement

When running the SonarCloud task Run Code Analysis, on our T-SQL code base, with T-SQL selected for .sql files, we get the following error message in Azure DevOps: Parse error in line…
And each time around CLUSTERED statement within the WITH part.

2021-06-07T08:55:39.8886375Z WARN: Unable to parse file ‘…/Tables/AT_MAP__PARTY.sql’ : Parse error at line 9 column 7:
2021-06-07T08:55:39.8886714Z
2021-06-07T08:55:39.8886992Z 1: CREATE TABLE [HDA_ReferenceData].[MAP_PARTY] (
2021-06-07T08:55:39.8887347Z 2: [ApplicationID] INT NULL,
2021-06-07T08:55:39.8887677Z 3: [Source] VARCHAR (60) NULL,
2021-06-07T08:55:39.8887991Z 4: [PartyNumber] VARCHAR (60) NULL,
2021-06-07T08:55:39.8888318Z 5: [Remark1] VARCHAR (256) NULL,
2021-06-07T08:55:39.8888646Z 6: [Remark2] VARCHAR (256) NULL,
2021-06-07T08:55:39.8888958Z 7: [Remark3] VARCHAR (256) NULL
2021-06-07T08:55:39.8889257Z 8: )
2021-06-07T08:55:39.8889541Z 9: WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = REPLICATE);
2021-06-07T08:55:39.8890027Z ^
2021-06-07T08:55:39.8890253Z 10:
2021-06-07T08:55:39.8890453Z 11:
2021-06-07T08:55:39.8890538Z
2021-06-07T08:55:39.8890979Z WARN: Unable to parse file ‘…/Tables/IndustryCodeMapping.sql’ : Parse error at line 15 column 2:
2021-06-07T08:55:39.8891370Z
2021-06-07T08:55:39.8891632Z 5: [IndustryTypeFrom] nvarchar NULL,
2021-06-07T08:55:39.8891952Z 6: [IndustryTypeTo] nvarchar NULL,
2021-06-07T08:55:39.8892240Z 7: [LastBatchId] [bigint] NULL,
2021-06-07T08:55:39.8892550Z 8: [ReportingPeriod] varchar NULL,
2021-06-07T08:55:39.8892839Z 9: [LoadDtTm] [datetime] NULL,
2021-06-07T08:55:39.8893148Z 10: [SourceInstanceCd] nvarchar NULL
2021-06-07T08:55:39.8893416Z 11: )
2021-06-07T08:55:39.8893625Z 12: WITH
2021-06-07T08:55:39.8893846Z 13: (
2021-06-07T08:55:39.8894109Z 14: DISTRIBUTION = HASH ( [IndustryCodeFrom] ),
2021-06-07T08:55:39.8894427Z 15: CLUSTERED COLUMNSTORE INDEX
2021-06-07T08:55:39.8894685Z ^
2021-06-07T08:55:39.8894887Z 16: )
2021-06-07T08:55:39.8895105Z 17: GO
2021-06-07T08:55:39.8895307Z 18:
2021-06-07T08:55:39.8895407Z
2021-06-07T08:55:39.8895839Z WARN: Unable to parse file ‘…/Stored Procedures/PrcCSApplicant.sql’ : Parse error at line 24 column 8:
2021-06-07T08:55:39.8896175Z
2021-06-07T08:55:39.8896393Z 14: ,@ReportingPeriod
2021-06-07T08:55:39.8896667Z 15: ,@IncludeDeleted = NULL
2021-06-07T08:55:39.8896905Z 16:
2021-06-07T08:55:39.8897207Z 17: EXEC [DAL_CS].Prc_CSApplicant_Deleted
2021-06-07T08:55:39.8897499Z 18:
2021-06-07T08:55:39.8897704Z 19:
2021-06-07T08:55:39.8898000Z 20: ---------------CSApplicant_Active---------------
2021-06-07T08:55:39.8898426Z 21: IF OBJECT_ID(‘tempdb…#CSApplicant_Active’,‘U’) IS NOT NULL DROP TABLE #CSApplicant_Active
2021-06-07T08:55:39.8898787Z 22:
2021-06-07T08:55:39.8899085Z 23: CREATE TABLE #CSApplicant_Active
2021-06-07T08:55:39.8899444Z 24: WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION=HASH( CSNbr ))
2021-06-07T08:55:39.8899763Z ^
2021-06-07T08:55:39.8899989Z 25: AS
2021-06-07T08:55:39.8900205Z 26: SELECT
2021-06-07T08:55:39.8900462Z 27: SourceInstanceCd
2021-06-07T08:55:39.8900726Z 28: ,[CSSrcRefCd]
2021-06-07T08:55:39.8901005Z 29: ,[CSNbr]
2021-06-07T08:55:39.8901282Z 30: ,[ApplicantNbr]
2021-06-07T08:55:39.8901539Z 31: ,[ApplicantName]
2021-06-07T08:55:39.8901812Z 32: ,[ApplicantTypeCd]
2021-06-07T08:55:39.8902080Z 33: ,[ApplicantRoleDescr]
2021-06-07T08:55:39.8902351Z 34: ,PointInTime

Indeed. The SQL server documentation doesn’t mention this syntax very explicitly but it seems to exist.
We’ll fix that: SONARTSQL-266

Thanks for the feedback!

1 Like

@pynicolas Hi, is there any progress on this item? I see the 266 report, but nothing happening?

Indeed, nothing was done so far to tackle this issue.
We’ll see if we can fix it in the near future.