Feature support

Feature support#

This page summarizes which Transact-SQL features Querona currently supports. Querona is focused on reading and integrating data through a SQL Server–compatible surface, so the data query language is broad, while procedural and data-processing constructs are intentionally limited.

Note

This page reflects the current state. Anything not listed as supported should be treated as not currently available.

Supported#

Queries

  • SELECT with DISTINCT, TOP (PERCENT / WITH TIES), column and table aliases

  • UNION / UNION ALL / EXCEPT / INTERSECT

  • Subqueries and derived tables

  • Non-recursive common table expressions — WITH name (columns) AS (...) on SELECT statements and inside view definitions, sharing one WITH with XMLNAMESPACES

  • WHERE predicates: comparison operators, IN, BETWEEN, LIKE (with ESCAPE), IS NULL, EXISTS, ALL / SOME / ANY

  • GROUP BY with ROLLUP, CUBE and GROUPING SETS; HAVING

  • ORDER BY with OFFSET / FETCH

  • Joins: INNER, LEFT / RIGHT / FULL OUTER, CROSS JOIN, CROSS APPLY / OUTER APPLY

Data modification

  • INSERT, UPDATE, DELETE

  • MERGE

  • SELECT ... INTO and CREATE TABLE AS SELECT (CTAS)

  • Rows-affected reporting on the wire (SqlDataReader.RecordsAffected / ExecuteNonQuery(), SSMS “(N rows affected)”) for SELECT and data-modification statements

Programmability and language elements

  • Variables (@var), table variables (DECLARE @t TABLE) and temporary tables (#tmp)

  • Control-of-flow: IF…ELSE, WHILE and BEGIN…END blocks

  • Inline table-valued functions (custom), and CLR table-valued functions used by built-in providers such as REST (see User-defined functions)

  • Explicit transactions: BEGIN TRANSACTION / COMMIT / ROLLBACK

Functions and expressions

  • Conversion with CAST and CONVERT (float and real styles 0, 1, 2, 3 and 126; style 129 is not supported); CASE, IIF, COALESCE, NULLIF, ISNULL

  • Aggregates including STRING_AGG, GROUPING

  • Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, with OVER (PARTITION BY, ORDER BY, ROWS / RANGE frames) and WITHIN GROUP

  • A broad set of date/time, string, mathematical and system/metadata functions (see Functions), including @@ROWCOUNT and ROWCOUNT_BIG()

  • Rowset functions that expose external data as a table in FROM: OPENQUERY (pass-through to a source), OPENROWSET (provider and BULK file forms) and OPENJSON

  • GENERATE_SERIES - a table of numbers, generated by Querona or by a SQL Server source (natively on SQL Server 2022 and later, as an equivalent rewrite on older versions)

  • STRING_SPLIT - a delimited string as rows

  • CROSS APPLY / OUTER APPLY over STRING_SPLIT, OPENJSON and GENERATE_SERIES with arguments taken from the rows of any source; where no source or federator runs the function, Querona evaluates it once per driving row

  • CROSS APPLY / OUTER APPLY over a REST function with arguments taken from the rows of any source: Querona calls the endpoint once per driving row (see User-defined functions)

  • JSON: ISJSON, JSON_VALUE, JSON_QUERY, JSON_PATH, JSON_PATH_EXISTS, OPENJSON and FOR JSON (see JSON)

  • XML: the xml data type, XML schema collections, FOR XML (RAW / AUTO / PATH), OPENXML, and the .value() / .query() / .exist() / .nodes() methods (see XML for the .nodes() column-source limitation)

DDL

  • CREATE / ALTER / DROP for tables, views, indexes (including columnstore), databases and schemas

  • CREATE / ALTER / DROP XML SCHEMA COLLECTION

  • UPDATE STATISTICS (bare or WITH FULLSCAN; see Statistics); TRUNCATE TABLE

Security and catalog

  • Permissions: GRANT / DENY / REVOKE

  • Row-level security and dynamic data masking

  • Linked servers (sp_addlinkedserver and querying linked servers)

  • sys.* catalog views, dynamic management views, and system stored procedures (see Reference)

  • Extended properties — custom name/value metadata on databases, schemas, tables, views, columns, indexes and foreign keys (sp_addextendedproperty / sp_updateextendedproperty / sp_dropextendedproperty, fn_listextendedproperty, sys.extended_properties)

Types

  • The standard SQL Server data types, including sql_variant and the geometry spatial type (see Data types and Spatial Types)

Not currently supported#

  • Recursive common table expressions; a WITH clause on INSERT / UPDATE / DELETE / MERGE

  • User-defined stored procedures (CREATE PROCEDURE); scalar user-defined functions; multi-statement table-valued functions

  • Cursors

  • TRY / CATCH, THROW, RAISERROR; TRY_CAST

  • CONTAINS / full-text search

  • FOR XML EXPLICIT; the XML .modify() method; XML indexes

  • JSON_MODIFY, JSON_OBJECT / JSON_ARRAY, JSON aggregate functions, and the native json data type

  • The geography and hierarchyid types

  • DBCC — by design: Querona keeps no internal database structures of its own and relies on the underlying engines, whose maintenance is performed with their native tools