SQL Server 2014: User Views – (Part-2)
Author: Basit A. Farooq
Editor’s Note: In this second part, you’ll learn about the SCHEMABINDING requirement for creating indexed views, which are persisted views. You’ll also learn that local partitioned views are being deprecated, though partitioned views based on federated servers are still commonly used.
Creating views by using SQL Server Management Studio
The following are the steps to create views with the SQL Server Management Studio graphic environment. There are four pages involved by default when defining a view, as shown in the following diagram:
- Diagram – Graphic representation of the base objects.
- Criteria – Column list and column criteria.
- SQL – SELECT statement to create the view.
- Results – SELECT statement result, used to test the query.
To create a view:
- Open SQL Server 2014 Management Studio.
- In Object Explorer, expand the Databases folder.
- Expand the database in which you want to create the view.
- Right-click on the Views folder and select New View… from the context menu. The Add Table dialog box now opens, as shown in the following screenshot:
- In the Add Table window, choose a base object (Table or View) and click Add.
- Continue choosing base objects until all necessary objects are selected.
- Click on Close button to close the Add Table dialog box. The Create View pane is now visible, which you can use to design your views. For example, using the Create View pane, you can perform the following:
- You can use the Diagram pane to select the view columns.
- You can use the Criteria pane to specify the view criteria, such as sort order and filter logic.
- You can use the SQL pane to directly modify the view query.
- Once satisfied with the changes, click on the save icon in the SQL Server 2014 Management Studio menu bar. This opens the Choose Name dialog box. Type in the name of the view and then click on OK to save the view.
- To verify the SELECT statement, right-click in any pane and choose Execute SQL. You use the Criteria Output check box to determine whether a column is a returned column or just used for defining the view. You can also control the view by directly editing the SELECT statement. Changes you make to the query are reflected in the Criteria pane automatically.
- Finally, click on the X in the corner of the new view tab to close the SQL Server 2014 Management Studio view designer graphical environment.
The ALTER VIEW/DROP VIEW statements
You can modify a view by:
- Dropping and recreating the view.
- Running ALTER VIEW statement to modify the view definition.
The ALTER VIEW syntax is the same as the CREATE VIEW syntax:
ALTER VIEW [schema.]view_name [(column_list)]
[WITH view_attributes]
AS select_statement [;]
[WITH CHECK OPTION]
To modify a view, you run ALTER VIEW with the new parameters. You can modify an earlier example and add the ShipDate column to the Sales.v_BusinessOrders view, by running:
ALTER VIEW Sales.v_BusinessOrders
WITH ENCRYPTION
AS
( SELECT o.OrderID ,
c.Name ,
o.OrderDate ,
o.ShipDate
FROM Sales.[Order Head] o ,
Sales.BusinessCustomer c
WHERE o.IsBusiness = 1
AND o.CustomerID = c.CustomerID
)
There is a potential problem with using ALTER VIEW. When you run ALTER VIEW on an indexed view, SQL Server drops all indexes on the view.
To drop a view, you use DROP VIEW with the syntax:
DROP VIEW [schema.]view_name
You can specify multiple views in the DROP VIEW statement. You must separate multiple views with commas. For example, run the following DROP VIEW statement to drop the Sales.v_BusinessOrders view.
Modifying and dropping views by using SQL Server Management Studio
To modify a view in SQL Server Management Studio:
- Expand the Views folder.
- Right-click on the view and choose Design from the context menu.
- Make changes to the Diagram, Criteria, and SQL panes, as necessary.
- Save the view changes.
Note: If the view was created using the WITH ENCRYPTION option, you cannot modify it in SQL Server 2014 Management Studio. The Design option is disabled when you right-click on an encrypted view.
To drop a view in SQL Server Management Studio:
- Expand the Views folder.
- Right-click the view and choose Delete or you can select a view and press Delete button on your keyboard.
- Click OK to verify your action.
Indexed views
An indexed view is a persisted view that is stored on disk. The indexed view helps improve performance because, as long as the data in the base objects has not changed, SQL Server can process queries based on views without accessing the base objects. SQL Server Database Engine automatically updates the indexed view indexes if the data in the key columns changes. The general syntax is the same as when creating any other type of view. The indexed view feature is available only in the Enterprise edition of SQL Server 2014.
The improved performance during queries doesn’t come without a cost:
- Indexed views require additional disk and processor overhead during creation, compared to standard view.
- View index storage requires additional disk space.
- When base table data changes in key columns, the database engine must update the view indexes.
When creating an indexed view, you should consider the following points:
- You must create the view using WITH SCHEMABINDING.
- The indexed view base objects cannot include other views
- You must reference base objects by two-part names (schema.object_name).
- You must create a clustered index on the view.
- The SELECT statement can’t include the UNION keyword or any subqueries.
- The LEFT, RIGHT, or FULL OUTER joins are not allowed for indexed view queries
For example, first create the view:
CREATE VIEW Sales.v_OrderList
WITH SCHEMABINDING
AS
( SELECT o.OrderId ,
b.[Name] ,
d.ItemID ,
d.PartNumber ,
d.Quantity
FROM Sales.[Order Head] o ,
Sales.[Order Detail] d ,
Sales.BusinessCustomer b
WHERE IsBusiness = 1
AND d.OrderID = o.OrderID
AND b.CustomerID = o.CustomerID
)
Then, create a clustered index on the view:
CREATE UNIQUE CLUSTERED INDEX ix_OrderList
ON Sales.v_OrderList(OrderID, ItemID, [Name], PartNumber)
Partitioned views
Before the introduction of partitioned tables in SQL Server 2005, you partitioned data by locating horizontally filtered tables on one or more servers. This is a feature of Enterprise edition of SQL Server. Partitioned tables are the preferred solution when you horizontally partition data on a single server. A partitioned view provides access to horizontally partitioned tables, consolidating the data. You can base a partitioned view on:
- Multiple tables from the same server (local partitioned view).
- Multiple tables from different servers and heterogeneous data sources (distributed partitioned view).
To create the view:
- Create a SELECT statement that retrieves the same columns from each table.
- Use the UNION ALL keyword to combine the SELECT statements.
The tables on which the view is based must meet the following requirements:
- Each table must have the same columns.
- Columns in each table must have the same data types.
- The primary key in each table must have the same number of columns.
- Tables can’t have overlapping data.
You still use partitioned views to support applications that are based on federated database servers—groups of servers working together to provide data services. In this distributed data environment, you horizontally partition data across multiple servers and can include heterogeneous (non-SQL Server) RDBMS servers. The servers involved can be in close proximity or can be spread across the country or even around the world. A detailed look at federated database server applications is beyond the scope of this article.
View information
You can retrieve information about views with the following catalog views:
- sys.views – Returns information about views.
- sys.columns – Returns information about column use in views.
- sys.sql_dependencies – Returns information about dependencies.
You can view the statement used to create a view with the sp_helptext system stored procedure. The syntax for the procedure is:
sp_helptext [@objname =] ‘name’
You can retrieve the statement used to create the Sales.v_Items view with the following:
sp_helptext ‘Sales.v_Items’
You can see views in SQL Server 2014Management Server. To do this:
- Expand the database.
- Expand views.
You can expand a view and access detailed column, trigger, index, and statistics Information. You can execute a query and return all view columns and rows by right-clicking the view and choosing Open View. You can display view dependencies by right-clicking the view and choosing View Dependencies. You can view the view properties in SQL Server 2014 Management Studio by right-clicking a view and choosing Properties. Views have three sets of properties:
- General – Read-only list of the view connection, description, ad options.
- Permissions – View and modify permissions assigned on views.
- Extended Properties – View extended properties (if any).
Conclusion
In this two part article series, you learned about system views and user-defined views, which include standard views, indexed views, and partitioned views. You learned how to use CREATE VIEW and appropriate uses for views. You learned the SCHEMABINDING requirement for creating indexed views, which are persisted views. You also learned that local partitioned views are being deprecated, though partitioned views based on federated servers are still commonly used.