SQL Server

SQL Server 2014: User Views – (Part-1)

by Basit Aalishan Masood-Al-Farooq

SQL Server 2014: User Views – (Part-1)

Author: Basit A. Farooq

Views provide you with flexible options for displaying data, such as limiting access to specific columns in a table, effectively hiding the remaining columns; and joining data from multiple tables, giving you a denormalized presentation from normalized data. Most often, you will create views based on one or more tables, views, or a combination of the two. You can also include computed columns in your views.

In addition to tables and views, you can include table-value functions and synonyms as data sources for your view. A table-valued function is a function that returns a table variable. A synonym is an alternate name for a local or remote database object, such as a table. You can assign different permissions to a synonym than those assigned to the base object.

In this first of the two part article series, you will learn about system views and user-defined views, which include standard views, indexed views, and partitioned views. You also learn how to use CREATE VIEW and appropriate uses for views.

Role of views

You use views to customize how you present data and prevent unauthorized access to sensitive data.

Advantages of views

Views have several advantages over providing users and applications direct access to base tables.

• Focus – The view displays only data that is appropriate to the user or that the user needs to see. You can limit the view for security reasons or simply avoid confusion by not presenting what would be, to the user, extraneous data.
• Custom access – You can present data in a way that is appropriate to the user. You can define different views for different users and applications even though the views access the same source tables.
• Data manipulation – You can present joins, procedures, and selections used on a regular basis as views. You can update or delete data directly through a view as long as you modify only one source table. You can modify multiple source tables, with support from a trigger, an executable database object.
• Data export – You organize data for export to other applications. This is especially helpful when exporting data from multiple source tables into a single destination.
• Data security – You limit user access because users can query and modify only data that is presented to them in the view. The remaining table data is unseen and inaccessible.

One of the most common reasons you use views is to restrict access to sensitive or inappropriate data. For example, you could limit access to inventory records so that users can see selling prices, but not the item cost.

View guidelines and limitations

You should consider several guidelines and limitations when you design, create, and use views. When you create views, the following limits apply:

• Views can contain no more than 1,024 columns.
• You can create a view in the current database only.
• You cannot create a view as a temporary object.
• You cannot base a view on a temporary table.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate an AFTER trigger with a view.
• You can nest views to no more than 32 levels.

An AFTER trigger is code that executes as the result of a data modification statement (INSERT, UPDATE, or DELETE). Nested views are views that are based on other views.

By default, view columns have the same names as table columns. You don’t have to specify the name of each view column unless:

• A column contains a derived (computed) value.
• Two or more columns have identical names.
• You want to give one or more columns different names than the underlying table column names.

In addition to these limits, there are limits that apply to the SELECT statements you use to create views. The SELECT statement cannot:

• Include COMPUTE or COMPUTE BY clauses.
• Include the INTO keyword.
• Include the ORDER BY clause, unless used with a TOP clause.
• Contain the TABLESAMPLE clause.

You should use inside joins only when using joins to create views. The result of outside joins in views is sometimes unpredictable.

There are several limits for when you use views and modify tables through views:

• You cannot run a full-text query against a view.
• You cannot modify a view unless it is based on at least one table.
• You cannot modify columns based on aggregate functions.
• You cannot modify columns based on derived values (computed columns).
• You must supply a value for every column identified as NOT NULL in the underlying table.
• You must use an INSTEAD OF trigger to modify multiple underlying tables.

An INSTEAD OF trigger is a block of code that runs in place of a data manipulation statement. When used with a view based on multiple tables, you can create an INSTEAD OF trigger that contains multiple modification statements, one for each underlying table.

Types of views

SQL Server supports five basic view types. The first two are system views. The remaining views are user-defined views. Supported views are:

• INFORMATION_SCHEMA views – Return system metadata. Default objects contained in each database’s INFORMATION_SCHEMA schema.
• Catalog system views – Return system metadata. Default objects contained in each database’s sys schema and used to view system table contents.
• Standard views – User created views usually based on tables and other views.
• Indexed views – Persisted user-created views. Requires schema-bound base objects.
• Partitioned views – Based on distributed, horizontally partitioned tables.

Microsoft is in the process of deprecating partitioned views on locally partitioned tables. Other than system views that let you view information about views, a discussion of system views is beyond the scope of this column.

Standard views

You can manage views with CREATE VIEW, ALTER VIEW, and DROP VIEW commands and SQL Server Management Studio.

The CREATE VIEW statement

The syntax for the CREATE VIEW statement is:

CREATE VIEW [schema.]view_name [(column_list)]
[WITH view_attributes]
AS select_statement [;]
[WITH CHECK OPTION]

Supported CREATE VIEW arguments are:

• schema – Schema in the current database, defaulting to the user’s default schema.
• view_name – Valid unique identifier.
• column_list – Column name list, defaulting to the base column names.
• WITH view_attributes – Additional creation options.
• AS select_statement – SELECT statement defining the view.
• WITH CHECK OPTION – Forces data modification statements on view to follow SELECT statement criteria.

The WITH CHECK OPTION clause ensures that data remains visible in the view after a data modification is committed.

The CREATE VIEW statement supports three optional view attributes. These are listed as follows:

• ENCRYPTION – Encrypts text of the CREATE VIEW statement so that the statement can’t be retrieved from sys.syscomments.
• SCHEMABINDING – Binds the view to the underlying object schema so that users can’t modify the base table(s) in any way that affects the view definition.
• VIEW_METADATA – Causes SQL Server to return metadata about the view instead of base table(s) to the DB-Library, ODBC, and OLE DB application programming interfaces (APIs).

Schema, used in the context of the SCHEMABINDING attribute, refers to the metadata defining the base table(s).

The following example creates a view based on a single table:

CREATE VIEW v_SeeInv
AS (SELECT PartNumber, Description, QuantityOnHand, UnitSell
FROM Sales.Inventory)

The next example is based on a join. You join the Sales.Order Head and Sales.BusinessCustomer tables on the CustomerID column. The WITH ENCRYPTION attribute causes SQL Server to store the CREATE VIEW statement as encrypted text.

CREATE VIEW Sales.v_BusinessOrders
WITH ENCRYPTION
AS (SELECT o.OrderID, c.Name, o.OrderDate
FROM Sales.[Order Head] o, Sales.BusinessCustomer c
WHERE o.IsBusiness = 1 AND o.CustomerID = c.CustomerID)

In the next example you must specify the column names because the view columns include a computed value based on an aggregate function:

CREATE VIEW Sales.v_ItemSummary
([Order Number], [Total])
AS (SELECT OrderID, SUM(Quantity*SellPrice)
FROM Sales.[Order Detail]
GROUP BY OrderID)

Queries on this view return a list of order numbers and the order total amount, the sum of the line item extended prices for each order.

Continue to Part-2…