Tabulify Sql Server View Support

Tabulify Sql Server View Support

About

Tabulify supports SQL Views in 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: 

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
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 with-dependencies operation on view will not see them.

Task Runner