CREATE TABLE: SPARSE MASKED WITH (...) column order (per MS docs) fails to parse; reversed order parses fine

[voytrex]

Summary

CREATE TABLE column definitions that combine SPARSE and MASKED WITH (FUNCTION = ...) fail to parse (Incorrect syntax near 'MASKED') when the clauses appear in the order documented by Microsoft's own T-SQL reference (COLLATE → SPARSE → MASKED WITH). The reversed, non-standard order (MASKED WITH (...) before SPARSE) parses without error. This looks like an order-handling gap in the column_definition grammar rather than a missing feature — both SPARSE and MASKED WITH are individually well supported.

Repro

CREATE TABLE t (
    c varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS SPARSE MASKED WITH (FUNCTION = 'default()') NULL
);

Minimal reduction (no COLLATE needed):

CREATE TABLE t (c varchar(100) SPARSE MASKED WITH (FUNCTION = 'default()') NULL);

TSql160Parser parser = new(initialQuotedIdentifiers: true);
using StringReader reader = new(sql);
TSqlFragment fragment = parser.Parse(reader, out IList<ParseError> errors);
// errors.Count == 1
// errors[0].Message == "Incorrect syntax near 'MASKED'."

Swapping the clause order makes the identical statement parse cleanly:

CREATE TABLE t (c varchar(100) MASKED WITH (FUNCTION = 'default()') SPARSE NULL);
-- errors.Count == 0

Per the official CREATE TABLE grammar reference (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql), the documented <column_definition> order is:

column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ]
    [ SPARSE ]
    [ MASKED WITH ( FUNCTION = 'mask_function' ) ]
    ...

i.e. SPARSE before MASKED WITH. That documented order is exactly the order that fails to parse.

Scope tested

Parser classes (package version 180.37.3) — tested all concrete TSqlParser subclasses against the repro:

Parser	Result
TSql80Parser / TSql90Parser	fails on SPARSE itself (expected — predates the feature)
TSql100Parser / TSql110Parser / TSql120Parser	Expected FILESTREAM but encountered MASKED instead.
TSql130Parser … TSql180Parser	Incorrect syntax near 'MASKED'.
TSqlFabricDWParser	Incorrect syntax near 'MASKED'.

Every parser class from TSql100Parser onward (i.e. every version where MASKED is otherwise supported) rejects the SPARSE MASKED WITH (...) ordering.

Package versions — reproduced with the default (TSql160Parser)/latest-appropriate parser across every published version from 170.3.0 through the current latest:

170.3.0, 170.12.0, 170.64.0, 170.121.2, 170.157.0, 170.191.0, 180.6.0, 180.18.1, 180.37.3

All affected, no regression/fix in any tested version.

Expected behavior

COLLATE ... SPARSE MASKED WITH (FUNCTION = ...) — the order documented in Microsoft's own T-SQL reference, and valid, functioning DDL against a real SQL Server / Azure SQL instance — should parse without error, matching the already-working reversed order.

Additional notes

• Searched existing issues for MASKED, SPARSE, and combinations thereof; found no duplicate.
• Encountered this while building a T-SQL static-analysis tool against a real-world database schema using Dynamic Data Masking on sparse columns (a documented, supported SQL Server feature combination).