---json
{
    "page_id": "r7xujce6agowit4t887cu"
}
---
====== Tabulify Sql Server View Support ======


===== About =====
Tabulify supports [[docs:resource:sql_view|SQL Views]] in [[docs:system:sqlserver:sqlserver|SQL Server]].


===== Features =====

Thanks to the below features, we correct/avoid the following SQL Server view constraints.
==== Automatic ORDER BY deletion ====


Before creating a view, we delete the ''ORDER BY''
if we don't find a `TOP`, `OFFSET` or `FOR XML` clause.

Why?

`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.
```



==== Not Qualified View Name====

We pass only the name as view identifier.

Why ? A view takes only 1 name and refuses any schema or database.

Syntax:
  * Correct
```
create view "view_name" as
```
  * Incorrect
```
create view "schema"."view_name" as
```

It solve this specific error:
```
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.
```

===== Other constraints =====

Unfortunately, we can't resolve all SQL View constraint.

When developing against SQL Server, you also need to take into account these constraints.

==== 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.
```


==== 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.