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

Inizia con la grammatica di column_definition e riproduci il problema usando TSql160Parser e l’istruzione CREATE TABLE minima. Confronta l’ordine documentato SPARSE MASKED con l’ordine invertito che funziona già nelle versioni del parser interessate. Il lavoro è completo quando l’ordine documentato viene analizzato con zero errori e il comportamento esistente continua a essere coperto.

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:

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).