SQL Server

The sq SQL Server driver implements connectivity for the Microsoft SQL Server and Azure SQL Edge databases. The driver implements all optional driver features.

Add source #

Use sq add to add a source. The location argument should start with sqlserver://. For example:

sq add 'sqlserver://sakila:p_ssW0rd@localhost?database=sakila'

Notes #

Active schema & catalog #

SQL Server supports the concepts of schema and catalog.

When executing a sq query, you can use --src.schema to specify the active schema (or catalog.schema).

$ sq src
@sakila/ms19

$ sq --src.schema=INFORMATION_SCHEMA '.TABLES'
TABLE_CATALOG  TABLE_SCHEMA          TABLE_NAME              TABLE_TYPE
sakila         dbo                   rental                  BASE TABLE
sakila         dbo                   customer_list           VIEW
sakila         dbo                   film_list               VIEW
# output truncated ^^

When --src.schema is provided, sq explicitly renders the schema name in the generated SQL query. So, the query above would be rendered to:

SELECT * FROM "INFORMATION_SCHEMA"."TABLES"

When --src.schema is not provided, sq doesn’t explicitly render a schema name in the SQL query. The following two queries return the same results, but are rendered differently:

-- $ sq '.actor'
SELECT * FROM "actor"

-- $ sq --src.schema=dbo '.actor'
SELECT * FROM "dbo"."actor"

schema() builtin caveat #

For other database implementations, such as Postgres, sq implements --src.schema by setting the default schema when opening the DB connection, in addition to explicitly rendering the schema name in the SQL query. However, SQL Server does not support setting a default schema on a per-connection basis; the default schema is a property of the DB user. Most of the time this different behavior is moot. However, one consequence is that the SLQ builtin schema() function always returns the user’s default schema, regardless of the value of --src.schema.

$ sq -H 'schema()'
dbo

$ sq -H --src.schema=INFORMATION_SCHEMA 'schema()'
dbo