Q&A

SQL Server 2022: Ledger, DDM and T-SQL Enhancements

• By David Ramel
• 07/10/2023

Data developers are still learning about all the new goodies that arrived with SQL Server 2022, which is chock full of new features and enhancements such as ledger for blockchain security.

Microsoft introduced ledger last August, saying: "We're making the data in SQL verifiable using the same cryptographic patterns seen in blockchain technology, while keeping the flexibility and performance of a traditional database. Ledger is centrally managed, and you can cryptographically attest to other parties that your data can be trusted and hasn't been tampered with."

While a game-changer for security, SQL Server 2022 also introduced new functionality around Dynamic Data Masking (DDM), T-SQL enhancements and more.

To help developers use all the new stuff in SQL Server 2022, Leonard Lobel, longtime Microsoft MVP and CTO of Sleek Technologies, will helm a session titled "What's New for Developers in SQL Server 2022" in the big, five-day, in-person Visual Studio Live! developer conference to be held in San Diego Aug. 7-11.

We caught up with Lobel to learn more about the 75-minute Aug. 9 presentation, at which attendees will learn:

• How to secure data using ledger tables
• How to apply granular permissions to DDM
• How to leverage new T-SQL enhancements

VisualStudioMagazine: What inspired you to present on the topic of what's new for developers in SQL Server 2022?
Lobel: I have been presenting developer-focused SQL Server sessions and workshops since SQL Server 2008. Every new release is loaded with innovative and useful features, and SQL Server 2022 is no exception. With powerful new capabilities just added in SQL Server 2022, I am very excited to share them with the tech community at large!

As an example, what is one especially significant new feature of SQL Server 2022 of interest to developers?
The special handling to test for NULL using the IS keyword, rather than the usual = for equality, has been a pain point for developers since the very beginning of the product.

When NULLs need to be considered, we've always been forced to include this additional IS NULL or IS NOT NULL syntax. This not only clutters T-SQL code, but forces the execution plan to use an index scan. In SQL Server 2022, we have a new IS DISTINCT FROM and IS NOT DISTINCT FROM syntax that not only handles both NULL and non-NULL values, but allows the execution plan to use an index seek (significantly better performance than an index scan).

How easy is it to incorporate blockchain-based security with ledger?
Very! Distributed blockchain solutions are typically complex and resource intensive, with high latency and low throughput. With SQL Server Ledger, the blockchain technology is centralized within the core SQL Server database engine, so there are no complex setup requirements, and delivers significantly better performance than traditional distributed blockchain scenarios.

How do new T-SQL enhancements help developers using SQL Server 2022?
Well, I've already mentioned IS DISTINCT FROM and IS NOT DISTINCT FROM. But there are a host of other useful T-SQL functions new in SQL Server 2022, including DATE_BUCKET (grouping dates by bucket size), DATETRUNC (truncating date/time portions), GENERATE_SERIES (manufacture a table of series data), LEAST/GREATEST (find the lowest/highest value across a set of columns), plus some helpful enhancements to existing functions STRING_SPLIT (retain ordinal position), FIRST_VALUE/LAST_VALUE (can now ignore NULLs), and SELECT...WINDOW (can now define reusable aliases for repetitive windowing definitions).

What's a major pain point experienced by developers who are just starting to work with SQL Server 2022?
Developers shouldn't experience any difficulties when embracing SQL Server 2022. It uses the same setup experience as previous versions of SQL Server. The new Azure Extension for SQL Server does add some complexity, but is entirely optional unless you want to integrate with Microsoft Defender for Cloud, Purview, and Azure Active Directory.

How do you see SQL Server evolving in the future?
I think we can expect to see more and more cloud integration as hybrid solutions are becoming increasingly common. Also, I think we'll see more and more IQP (intelligent query processing) features to improve existing workloads at scale with no code changes, just as we've been seeing in every release since SQL Server 2017.

Note: Those wishing to attend the conference can save hundreds of dollars by registering early, according to the event's pricing page. The event organizer said: "Register for VSLive! San Diego by the Early Bird Deadline (July 14) to save up to $300 and secure your seat for intensive developer training in sunny Southern California!"