Sql Server

Tabulify supports Sql Server as source and target.

How to start a local SqlServer ?

To use the Howto sqlserver connection, you can start a local SqlServer with the following docker command:

docker run \
    -e MSSQL_SA_PASSWORD=TheSecret1! \
    -e ACCEPT_EULA=Y \
    -p 1433:1433 \
    -d \
    --name sqlserver \
    mcr.microsoft.com/mssql/server:2022-CU18-ubuntu-22.04

Support and Sql Server Features

Varchar

Tabulify modifies the default length for VARCHAR and NVARCHAR from 1 to max

max is the default for most database and avoid many problem when the length is not defined (ie nobody wants to store 1 character in a varchar).

Upsert

SqlServer does not use the SQL standard insert on conflict statement but a merge statement.

This merge statement requires a ON condition. Tabulify sets it to the first unique key constraint and if none is found, a standard insert statement is used.

Date/Time Sql Type

We map:

• the standard sql timestamp type to the datetime2 sql server type
• the standard sql timestamp with timezone type to the datetimeoffset sql server type

Because Sql Server does not support time with time zone, we translate it to the time sql server type when creating a table from a third database.

Unable to truncate a table with a foreign key

Sql Server does not support the truncate operations on a table that has a foreign key even if the foreign table is empty.

You would get this kind of error:

Cannot truncate table 'master.dbo.d_date' because it is being referenced by a FOREIGN KEY constraint.

If you want to truncate, you need to drop and recreate

View dependency not supported

Sql Server does not support dependencies on view (ie dependent on the table used in the view), meaning any with-dependencies operation on view will not see them.

Order By Not supported in derived tables

Order By is not supported by Sql Server in derived table such as:

• views,
• inline functions,
• derived tables,
• subqueries,
• and common table expressions

unless TOP, OFFSET or FOR XML is specified.

Example: if you create this view:

create view "error_orderby" as 
select 
   "cat_id", 
   "cat_desc", 
   "load_timestamp" 
from "master"."dbo"."d_category" 
   order by "cat_id"

You would get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, 
and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Aliases in View are mandatory

Sql Server requires an alias for each select expression in a query.

For instance:

• this query

create view "alias_error" as 
select 
   avg(sales_price), 
   avg(sales_qty) 
from "f_sales"

• would return this error:

Create View or Function failed because no column name was specified for column 1.