SQL Server 2022 gets data virtualization, REST APIs with PolyBase

Microsoft SQL Server written on a red background with related graphics in the background

Last year, Microsoft's 2022 variant of the SQL Server entered its initial preview cycle, before being made more publicly accessible earlier this year. Touted as its "most cloud-connected release", SQL Server 2022 enables deeper integration with services such as Azure Purview, Synapse Link, and more.

The platform also leverages the PolyBase service, which was first released in 2017 as a way of allowing T-SQL queries from external data sources to be processed internally in SQL Server instances. This is done without any additional software having to be installed to maintain a connection between the clients.

Today, the latest version of PolyBase has been released in SQL Server 2022, boasting new features such as data virtualization, querying data at the locations where it is resident, and usage of REST APIs. Furthermore, additional capabilities including support for various file formats on S3-compliant storage services, new commands, and more T-SQL-specific enhancements have been remarked upon as well.

By leveraging data virtualization via PolyBase, Microsoft offers the use of SQL Server security features, as well as cost-efficiency due to PolyBase being available already in SQL Server 2022. Similarly, all benefits that are gained through T-SQL can be utilized, with no data movement and a single source of data, allowing for ease of environment maintenance as well.

The aforementioned new T-SQL commands include:

CREATE EXTERNAL TABLE (CET) - Creates a table where the data stays in its original location outside of SQL Server

CREATE EXTERNAL TABLE as SELECT (CETAS) - [...] Allows SQL Server to transform and convert a given data stored inside or outside the database. [...] It then exports the data to a different location, either a network location or Azure. Finally, it creates an external table targeting the newly exported data

OPENROWSET - Lightweight command that allows SQL engine to access data outside SQL Server, either a file or another database

Data sources that will use REST API implementations are Azure Storage Account v2 (abs), Azure Data Lake Gen2 (ADLs), and more. Meanwhile, the new external file formats supported are CSV, Parquet, and Delta type.

To check out all these new features, you can join the SQL Server 2022 preview by following the registration process here.