SQL Server Adapter
Generate T-SQL for Microsoft SQL Server and Azure SQL from your model.
What the adapter emits
- Identifiers in
[brackets]; bare column names in expressions are lowercased. - Grain truncation as
DATETRUNC(month, x)(SQL Server 2022 and Azure SQL); parts withYEAR(x),DATEPART(QUARTER, x),DATEPART(WEEKDAY, x). - CTEs for the per-fact subqueries and a
FULL OUTER JOINwhen a query blends measures from two facts. - No array functions:
expression.array: trueis not supported on this adapter.
Configuration
sqlserver_db:
adapter: sqlserver
name: SQL Server Database
tier: warm
host: server.database.windows.net
port: 1433
database: analytics
username: analyst
# password: never here; zsql deploy strips it if present
Azure SQL uses the same adapter; the difference is in how your application connects, not in the SQL.
Connection fields
The server never connects to SQL Server: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.
zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It takes username and a password. TLS is not wired up for this driver yet. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips password from the file if present. See Connecting to a warehouse.
Notes
- Expressions are T-SQL.
count_big,iif(),string_aggandtry_castare fine inexpression.sql. - Schema-qualified tables go in
physical_name(dbo.store_sales).