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

  • 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)

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; 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)

  • 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

  • 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() methods

DDL

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

  • CREATE / ALTER / DROP XML SCHEMA COLLECTION

  • CREATE / UPDATE 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#

  • Common table expressions (WITH)

  • 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 .nodes() and .modify() methods; 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