Unofficial SQL ScriptDom change log

Disclaimer: this is an unofficial change log – it is provided AS-IS and should not be in any way considered as an official Microsoft statement.

I have been blogging about the ScriptDom parser for Microsoft (and Azure) SQL family of products and services. While the library is formally documented, some external guidance is needed, so I have added some code samples in the past to help get started. Apart from my posts here, there are some great blog posts like this ongoing series from Mala, and my own article on the Azure SQL dev blog.

ScriptDom evolves along with the product – as we add new T-SQL syntax to Azure SQL DB, Synapse SQL pools, or SQL Server, the ScriptDom library is usually updated. A convenient ship vehicle (for these updates to ScriptDom) is the DacFx NuGet. While some updates to the library are internal fixes, the ones that interest me (and probably most readers) the most are when there are new classes / properties / enumerations added to ScriptDom to keep up with the underlying changes to the T-SQL grammar and / or new capabilities, in the MS SQL family of products.

It is important to know about the DacFx repo on GitHub. While DacFx overall, and ScriptDom specifically, are still closed source, the DacFx repo does open up a direct line of communication to the concerned developers at Microsoft. What would be great is to have an official change log there for the changes to DacFx / ScriptDom. I have logged a request for the DacFx to consider doing this, but currently there is none that I know of, at least not to the level of detail that I described earlier.

Till such time that we have a detailed, fully updated change log, this blog post is being written as an unofficial change log for ScriptDom at least. Hopefully it will help readers understand when certain T-SQL grammar was added to ScriptDom, etc. I hope to keep it updated as we have later releases of DacFx and thereby, ScriptDom. If you have questions / feedback for me, please do leave a comment in this blog post and I will try to address it in due course of time.

Note that if there are no functionality changes for a given DacFx release, it will not feature in this table.

And again, this is an unofficial change log, so it is provided as-is and should not be in any way construed as an official Microsoft statement.

Note: the table below has the most recent changes at the end.

DacFx NuGet version Change Log Commentary
150.4897.1 Added:
* ClassifierEndTimeOption with members: Time, OptionType
* ClassifierImportanceOption with members: Importance, OptionType
* ClassifierMemberNameOption with members: MemberName, OptionType
* ClassifierStartTimeOption with members: Time, OptionType
* ClassifierWlmContextOption with members: WlmContext, OptionType
* ClassifierWlmLabelOption with members: WlmLabel, OptionType
* ClassifierWorkloadGroupOption with members: WorkloadGroupName, OptionType
* CreateWorkloadClassifierStatement with members: ClassifierName, Options
* DropWorkloadClassifierStatement with members: Name, IsIfExists
* WlmTimeLiteral with members: TimeString
* WorkloadClassifierOption with members: OptionType
* WorkloadClassifierOptionType with members: value__, WorkloadGroup, MemberName, WlmLabel, WlmContext, StartTime, EndTime, Importance
* WorkloadClassifierStatement with members: ClassifierName, Options
Changes related to Azure Synapse Analytics workload management (CREATE WORKLOAD CLASSIFIER; DROP WORKLOAD CLASSIFIER etc.)
150.4926.2-preview Added:
* CreateExternalStreamingJobStatement with members: Name, Statement
* CreateExternalStreamStatement with members: Name, Location, InputOptions, OutputOptions, ExternalStreamOptions
* DropExternalStreamingJobStatement with members: Name, IsIfExists
* DropExternalStreamStatement with members: Name, IsIfExists
* ExternalStreamingJobStatement with members: Name, Statement
* ExternalStreamLiteralOrIdentifierOption with members: Value, OptionKind
* ExternalStreamOption with members: OptionKind
* ExternalStreamOptionHelper with members:
* ExternalStreamOptionKind with members: value__, DataSource, FileFormat
* ExternalStreamStatement with members: Name, Location, InputOptions, OutputOptions, ExternalStreamOptions

Changed:

* DatabaseOptionKind has new properties: DataRetention
* ExternalFileFormatType has new properties: JSON

SQL Edge streaming:CREATE EXTERNAL STREAM etc.

Also adds JSON as an EXTERNAL FILE FORMAT.

150.4946.1-preview Added:
* DataRetentionTableOption with members: OptionState, FilterColumn, RetentionPeriod, OptionKind

Changed:

* TableOptionKind has new properties: DataRetention

Data retention options for SQL Edge.
150.5078.1-preview Added:
* AlterExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* CreateExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* DropExternalLibraryStatement with members: Name, Owner
* ExternalLibraryFileOption with members: Content, Path, Platform
* ExternalLibraryStatement with members: Name, Language, ExternalLibraryFiles

Changed:

* AdHocTableReference has new properties: ForPath
* BuiltInFunctionTableReference has new properties: ForPath
* BulkOpenRowset has new properties: ForPath
* ChangeTableChangesTableReference has new properties: ForPath
* ChangeTableVersionTableReference has new properties: ForPath
* DataModificationTableReference has new properties: ForPath
* FullTextTableReference has new properties: ForPath
* GlobalFunctionTableReference has new properties: ForPath
* InlineDerivedTable has new properties: ForPath
* InternalOpenRowset has new properties: ForPath
* OpenJsonTableReference has new properties: ForPath
* OpenQueryTableReference has new properties: ForPath
* OpenRowsetTableReference has new properties: ForPath
* OpenXmlTableReference has new properties: ForPath
* PivotedTableReference has new properties: ForPath
* QueryDerivedTable has new properties: ForPath
* SchemaObjectFunctionTableReference has new properties: ForPath
* SemanticTableReference has new properties: ForPath
* SqlScriptGeneratorOptions has new properties: AllowExternalLibraryPaths
* TableReferenceWithAlias has new properties: ForPath
* TableReferenceWithAliasAndColumns has new properties: ForPath
* UnpivotedTableReference has new properties: ForPath
* VariableMethodCallTableReference has new properties: ForPath
* VariableTableReference has new properties: ForPath

CREATE EXTERNAL LIBRARY, DROP EXTERNAL LIBRARY etc. (SQL Server ML Services.The ForPath changes are related to SQL Graph query, specifically when SHORTEST_PATH is used, then the non-anchor tables need to marked as FOR PATH.
150.5084.2 Removed:
* AlterExternalLibraryStatement
* CreateExternalLibraryStatement
* DropExternalLibraryStatement
* ExternalLibraryFileOption
* ExternalLibraryStatement

Changed:

* SqlScriptGeneratorOptions has deleted properties: AllowExternalLibraryPaths

It is rare that Microsoft removes functionality from ScriptDom, but that can happen for features which are in preview and therefore changing all the time. In this reelase, it looks like some of the previous changes in 150.5078.1-preview related to external library management were removed in this release. They make a re-appearance soon after though in 150.5141.1-preview. The older release being a preview one, it is perhaps not surprising that the interim changes did not make it through to this stable release.
150.5132.1-preview Added:
* PredictRunTimeOptions with members: ONNX
* PredictTableReference with members: ModelVariable, ModelSubquery, DataSource, RunTime, SchemaDeclarationItems, Alias, ForPath

Changed:

* FromClause has new properties: PredictTableReference

PREDICT support for ONNX runtime as well as being able to recognize the PREDICT TVF in the context of a FROM clause.
150.5141.1-preview Added:
* AlterExternalLanguageStatement with members: Platform, Operation, Owner, Name, ExternalLanguageFiles
* AlterExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* CreateExternalLanguageStatement with members: Owner, Name, ExternalLanguageFiles
* CreateExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* DropExternalLanguageStatement with members: Name, Owner
* DropExternalLibraryStatement with members: Name, Owner
* ExternalLanguageFileOption with members: Content, FileName, Path, Platform, Parameters, EnvironmentVariables
* ExternalLanguageStatement with members: Name, ExternalLanguageFiles
* ExternalLibraryFileOption with members: Content, Path, Platform
* ExternalLibraryStatement with members: Name, Language, ExternalLibraryFiles

Changed:

* SqlScriptGeneratorOptions has new properties: AllowExternalLanguagePaths, AllowExternalLibraryPaths

The ML Services related external library statements make a comeback. In addition, we see CREATE EXTERNAL LANGUAGE and related statements for the first time in ScriptDom. This is related to the SQL Server Language Extensions feature.
150.5164.1 Removed:
* AlterExternalLanguageStatement
* AlterExternalLibraryStatement
* CreateExternalLanguageStatement
* CreateExternalLibraryStatement
* DropExternalLanguageStatement
* DropExternalLibraryStatement
* ExternalLanguageFileOption
* ExternalLanguageStatement
* ExternalLibraryFileOption
* ExternalLibraryStatement

Changed:

* SqlScriptGeneratorOptions has deleted properties: AllowExternalLanguagePaths, AllowExternalLibraryPaths

For the second time, we see a removal of the external library and external language related classes 😊
150.5282.3 Added:
* AlterExternalLanguageStatement with members: Platform, Operation, Owner, Name, ExternalLanguageFiles
* AlterExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* CreateExternalLanguageStatement with members: Owner, Name, ExternalLanguageFiles
* CreateExternalLibraryStatement with members: Owner, Name, Language, ExternalLibraryFiles
* DropExternalLanguageStatement with members: Name, Owner
* DropExternalLibraryStatement with members: Name, Owner
* ExternalLanguageFileOption with members: Content, FileName, Path, Platform, Parameters, EnvironmentVariables
* ExternalLanguageStatement with members: Name, ExternalLanguageFiles
* ExternalLibraryFileOption with members: Content, Path, Platform
* ExternalLibraryStatement with members: Name, Language, ExternalLibraryFiles
* LedgerOption with members: OptionState, OptionKind
* LedgerTableOption with members: OptionState, AppendOnly, LedgerViewOption, OptionKind
* LedgerViewOption with members: ViewName, TransactionIdColumnName, SequenceNumberColumnName, OperationTypeColumnName, OperationTypeDescColumnName, OptionKind

Changed:

* DatabaseOptionKind has new properties: Ledger
* GeneratedAlwaysType has new properties: SequenceNumberEnd, SequenceNumberStart, TransactionIdEnd, TransactionIdStart
* IndexDefinition has new properties: IncludeColumns
* SqlScriptGeneratorOptions has new properties: AllowExternalLanguagePaths, AllowExternalLibraryPaths

ML Services related statements are back (again!) and also some new statements to support early releases of the SQL Ledger feature.
150.5290.2-preview Removed:
* LedgerOption
* LedgerTableOption
* LedgerViewOption

Changed:

* DatabaseOptionKind has deleted properties: Ledger
* GeneratedAlwaysType has deleted properties: SequenceNumberEnd, SequenceNumberStart, TransactionIdEnd, TransactionIdStart
* IndexDefinition has deleted properties: IncludeColumns

Ledger options got seem to have been removed for now in this release. Again this is not uncommon as both the feature itself (Ledger) and the DacFx version are both preview.
150.5332.2-preview Added:
* LedgerOption with members: OptionState, OptionKind
* LedgerTableOption with members: OptionState, AppendOnly, LedgerViewOption, OptionKind
* LedgerViewOption with members: ViewName, TransactionIdColumnName, SequenceNumberColumnName, OperationTypeColumnName, OperationTypeDescColumnName, OptionKind

Changed:

* DatabaseOptionKind has new properties: Ledger
* GeneratedAlwaysType has new properties: SequenceNumberEnd, SequenceNumberStart, TransactionIdEnd, TransactionIdStart
* IndexDefinition has new properties: IncludeColumns

Ledger related options are back again.
160.5196.4-preview Added:
* Sql160ScriptGenerator with members: Options
* TSql160LexerInternal with members: CurrentOffset
* TSql160Parser with members: QuotedIdentifier
* TSql160ParserBaseInternal with members: FragmentFactory, PhaseOne
* TSql160ParserInternal with members: FragmentFactory, PhaseOne
* WindowClause with members: WindowDefinition
* WindowDefinition with members: WindowName, RefWindowName, Partitions, OrderByClause, WindowFrameClause

Removed:
* LedgerOption
* LedgerTableOption
* LedgerViewOption

Changed:
* DatabaseOptionKind has deleted properties: Ledger
* GeneratedAlwaysType has deleted properties: SequenceNumberEnd, SequenceNumberStart, TransactionIdEnd, TransactionIdStart
* IndexDefinition has deleted properties: IncludeColumns
* OverClause has new properties: WindowName
* QuerySpecification has new properties: WindowClause
* SqlScriptGeneratorOptions has new properties: NewLineBeforeWindowClause
* SqlVersion has new properties: Sql160
* SqlVersionFlags has new properties: TSql160, TSql160AndAbove, TSqlUnder160

The Sql160 family makes its first appearance. This means the TSql160Parser is available and can and should be used to parse the latest and greatest syntax. Also looks like some new SqlVersion enumeration values got added corresponding to 160.We also see the first preview appearance of the window functions enhancements for SQL Server 2022 like the WINDOW clause.
160.5323.3-preview Added:
* LedgerOption with members: OptionState, OptionKind
* LedgerTableOption with members: OptionState, AppendOnly, LedgerViewOption, OptionKind
* LedgerViewOption with members: ViewName, TransactionIdColumnName, SequenceNumberColumnName, OperationTypeColumnName, OperationTypeDescColumnName, OptionKind
* OpenRowsetColumnDefinition with members: ColumnOrdinal, JsonPath, ColumnIdentifier, DataType, Collation
* OperatorAuditOption with members: Value, OptionKind

Changed:

* AuditOptionKind has new properties: OperatorAudit
* BulkInsertOptionKind has new properties: DataCompression, HeaderRow, ParserVersion, RowsetOptions
* BulkOpenRowset has new properties: WithColumns
* DatabaseOptionKind has new properties: Ledger
* GeneratedAlwaysType has new properties: SequenceNumberEnd, SequenceNumberStart, TransactionIdEnd, TransactionIdStart
* IndexDefinition has new properties: IncludeColumns

The SQL Ledger related T-SQL syntax, such as append-only ledger tables, are reflected in this release of ScriptDom.

The OperatorAudit is related to auditing for Microsoft initiated devops activities as described in this post.

Some of the WITH … (column) enhancements in OPENROWSET to allow specifying column metadata – this is specific to Azure Synapse Serverless SQL Pools. There are other changes in ScriptDom to keep up with the OPENROWSET support in Azure Synapse Serverless SQL Pools. See the docs for more details. In this process, I learnt that the parser version 2.0 for Synapse offers a higher degree of control for parsing files, also there is a way to specify that you are reading GZIP compressed files, etc.