====== Sql Server ======



Tabulify supports [[https://learn.microsoft.com/en-us/sql/|Sql Server]] as [[[[:docs:flow:source|source]] and [[:docs:flow:target|target]].

===== How to start a local SqlServer ? =====

To use the [[:docs:connection:howtos|Howto sqlserver connection]], you can start a local SqlServer with the following docker command:

```bash
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  [[https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql|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 [[https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16|datetime2]] sql server type
  * the standard sql `timestamp with timezone` type to the [[https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql|datetimeoffset]] sql server type

Because Sql Server does not support `time with time zone`, we translate it to the [[https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql|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 [[docs:op:truncate|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 [[:docs:op:drop|drop]] and [[docs:op:create|recreate]]

==== View dependency not supported ====

Sql Server does not support dependencies on view (ie dependent on the table used in the view), meaning any [[:docs:op:with-dependencies|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:
```sql
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
```sql
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.
```