It has been over 3 months now that SQL Server 2014 is generally available. The well-known and much talked about features such as in-Memory OLTP and Clustered ColumnStore indexes are big motivations to move your workload to the new platform.

With the new features, the T-SQL language surface has also changed of course. Under compatibility level 120, there are new grammar elements to consider. I recently went through one such round of impact analysis, specifically around the ScriptDom parser.

The good news is that the move from SQL 2012 ScriptDom to the latest one is very incremental and almost all of the changes are only due to new syntax elements. I prepared a list of these statements (as depicted in the parser) for your convenience.

 

In-Memory OLTP support

No coincidence that the changes related to in-Memory OLTP are the biggest! This section also includes related support such as ATOMIC block statements, delayed durability, inline index definitions etc.

• AlterDatabaseAddFileGroupStatement.ContainsMemoryOptimizedData Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• FileGroupDefinition.ContainsMemoryOptimizedData Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• IdentifierAtomicBlockOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AtomicBlockOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AtomicBlockOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• ProcedureOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• ProcedureOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• BeginEndAtomicBlockStatement Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• ColumnDefinition.Index Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• IndexDefinition Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• UniqueConstraintDefinition.IndexType Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableDefinition.Indexes Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableHintKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• IndexOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• CommitTransactionStatement.DelayedDurabilityOption
• DatabaseOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DelayedDurabilityDatabaseOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• DelayedDurabilityOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DurabilityTableOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• DurabilityTableOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DatabaseOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DatabaseOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• CreateTypeTableStatement.Options Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• LiteralAtomicBlockOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• MemoryOptimizedTableOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• NullableConstraintDefinition Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• OnOffAtomicBlockOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Buffer Pool Extension, Resource Governor, HADR and FCI related Server DDL

This set of changes has to do with the support for configuring the BPE, I/O parameters in Resource Governor, and some minor but important DDL to configure HADR availability groups and FCI instances from T-SQL itself.

• AlterServerConfigurationBufferPoolExtensionOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationBufferPoolExtensionOption.OptionKind Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationSetBufferPoolExtensionStatement Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• ResourcePoolParameterType Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationDiagnosticsLogOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationDiagnosticsLogOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationSetDiagnosticsLogStatement Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationFailoverClusterPropertyOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationFailoverClusterPropertyOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationSetFailoverClusterPropertyStatement Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationHadrClusterOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationHadrClusterOption.OptionKind Property (Microsoft.SqlServer.TransactSql.ScriptDom)
• AlterServerConfigurationSetHadrClusterStatement Class (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Clustered ColumnStore related

This group includes the index DDL and Archive compression related changes.

• IndexOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DataCompressionLevel Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• DataCompressionLevel Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• IndexType Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• IndexTypeKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Incremental Statistics related

• AutoCreateStatisticsDatabaseOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• StatisticsOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• StatisticsPartitionRange Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• ResampleStatisticsOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• OnOffStatisticsOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Managed Lock Priority related

Managed lock priority changes extend to quite a few operations such as index maintenance, partition switch etc.

• AbortAfterWaitType Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• LowPriorityLockWaitOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• LowPriorityLockWaitOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• LowPriorityLockWaitTableSwitchOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• OnlineIndexLowPriorityLockWaitOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• OnlineIndexOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableSwitchOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• TableSwitchOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Backup Encryption + Security DDL

Here you notice the changes to support encryption while backing up; and also the new encryption algorithm enumeration to support the same. We also have several new event notifications available.

• BackupEncryptionOption Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• BackupOptionKind Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)

• EncryptionAlgorithm Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• EventNotificationEventGroup Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• EventNotificationEventType Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• EventNotificationEventType Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)
• EventNotificationEventType Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)

 

Miscellaneous

• OptionValue Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• OnOffOptionValue Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• LiteralOptionValue Class (Microsoft.SqlServer.TransactSql.ScriptDom)
• SqlVersion Enumeration (Microsoft.SqlServer.TransactSql.ScriptDom)

In conclusion, while I believe I have covered all the changes (and I’m glad there seem to be none which are BREAKING changes as such) – if you still find something which has been added or changed and I did not mention it in this post, please feel free to leave a comment below!