Aims of this Article
- Improve understanding views in SQL 2000/2005
- Explain how views can improve database security
- Explain how to encrypt views so that source tables cannot be seen
- Demonstrate building views using
- T-SQL statements
- Microsoft SQL 2000 Enterprise manager
- SQL 2005 Management studio
- Explain and demonstrate how to join two tables within a view
- Demonstrate sub queries within a view
What is a View?
A view is simply a query stored as an object in the database. In essence a view can be thought of a virtual table, that in itself does not contain any data or information. A view can return the same data that we could from any SELECT statement. Views are implemented as stored SELECT statements that have virtually no overhead because they don't actually store data. However, a view is not a tool for processing data using T-SQL commands, like a stored procedure is. A view is only able to hold one query at a time. Therefore, a view is more of a query than a stored procedure. Just as with a stored procedure or a query within a Query Editor pane, you can include tables from databases that are running on different servers. Providing the user ID has the necessary security credentials, it is possible to include tables from several databases.
Where Do Views Come From and Why?
Views have been available throughout the history of Microsoft SQL Server. When working on a fully normalised database and querying the data, there is often a requirement to write complex joins, aggregates, functions and plethora of other T_SQL functions to produce the results. To mask this complexity, this statement can be created as a view, and then future queries can be preformed against the view.
Products like Oracle have come out with the concept of a Materialized View that give an additional performance. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a subquery. A T-SQL boost to this problem is by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle's Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle's Materialized Views.
Advantages of Views
- Restrict data access and/or simplify data access
A view can be used to limit the user to only use a few columns in a table. For example if we do not want a user to be able to access all columns because of security. But it could also be because not all columns are interesting for the user. It is also possible to limit access to rows, using a WHERE clause. If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the users use is the view's name. Simple, but powerful! - Simplify data manipulation
We can also use a view to manipulate data. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved. - Import and export data
A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view. - Merge data
A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator. For example if we had customers in Europe and United States, we could have one server for Europe, and one for United States, with tables that are identical, and then merge all data in a partitioned view.
Are There Any Guidelines for Creating Views?
Choose an intuitive name that defines your view, such as dbo.Customer_Order_View, the owner name is optional, but it should be specified to avoid broken ownership chains (assuming the referenced DB objects are owned by DBO). When a view is created, the existence of objects referenced is verified. SELECT permission on the object are required if the owner differs from that of the view. If the view references derived columns, or if columns from multiple tables have the same name, column names must be specified. Column names can be aliased in order to make them user friendly.
- TIP: Before creating the view, test the
select statement to make sure it returns desired results. It may also be a good time to test the performance of the query.
Are There Restrictions on Creating a View?
- Create view statement cannot contain the
INTO keyword or the COMPUTE/COMPUTE BY clauses ORDER BY clause can only be specified in conjunction with the TOP keyword. - Views cannot be combined in Batch T-SQL statements
- The maximum number of columns a view can reference is 1024
- A view cannot reference any temporary tables
- Views can only be created in the current database.
- The
create view statement does not allow you to specify a database name.
How Do I Create a View?
Views can be created in a variety of ways depending on which version of SQL server you are using:
- Using T-SQL statements
- Using Enterprise manager
- Using SQL 2005 Management studio
How Do I Create a View using T-SQL Syntax?
CREATE VIEW owner.viewname [(Column[,n])]
select statement
[WITH CHECK OPTION]
Example
CREATE VIEW uk_employee_view
SELECT * FROM employee WHERE country = 'UK'
WITH CHECK OPTION
What is the WITH CHECK OPTION ?
If a view has been defined with the WITH CHECK OPTION, any modification to the underlying data can result in that data no longer being accessible through the view. This forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.
Create a View using SQL 2000 Enterprise Manager
You can create views through the enterprise manager by accessing the view designer. To access the view designer, right click on the views in the appropriate database and select new view. The view designer is actually based on the Query designer with one slight difference - when you select save, you are prompted for a name for the view and the query you have built is stored as the view definition.
After you are in the view designer, you can click on the Add Table icon (it's the one to the far right in the toolbar). Selecting the check boxes for the appropriate columns adds them to the select statement that will be saved as your view definition.
To change the properties of the view, i.e. adding the WITH CHECK option, select the properties icon or right click and select properties from the pop up menu.
Creating a View using SQL2005 Management Studio
In the SQL Management studio, navigate to the database you would like to create the view in. Navigate to the views node in the treeview and right click it. This will bring up a pop menu with New View as an option. From here, the procedure is very similar to the SQL 2000 one.
How Can I Use a View as Security?
As a developer, one of your concerns when building a view is what happens when someone makes changes to the structure of an underlying table used in your view. This causes problems. I will show you how to get around this problem and secure the build of a view so that this sort of thing doesn't happen.
How and Why Would I Encrypt View Definitions?
The most common situation where you will find views encrypted is when using a view
to return specific information from a database. You also don't wish anyone to see how that information was returned, for example not allowing contractor application developers see the structure of a source database. Encrypting the T-SQL code that makes up the view, which would mean that how the information was being returned would not be visible.
- TIP: Before encrypting a view, ensure that you have a copy of the original source safely tucked away in your source control system as once the view has been encrypted it difficult to revert back to it's original state.
Indexing a View
Just as tables can be indexed so can views, and the rules for choosing columns to make indexes are similar to those for a table, however there are some major requirements one needs to meet before one can index a view.
The first index to be created must be a unique clustered index, once this index has been created, additional non clustered indexes on the view can be created. An important note to bear in mind here is that if you drop the clustered index on the view, then all subsequent indexes on the view will be automatically dropped. Also if we drop a view, all the indexes will be dropped too. The view that the index is to be built on must only contain tables and not other views. The tables must all come from one database, and the view must also reside in that database and have been built with the SCHEMABINDING option.
The following options must be set to ON while creating an index on a view. These options need only be set to ON for that session and therefore would precede the CREATE INDEX statement.
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
SET IGNORE_DUP_KEY OFF
To be continued...
History
- 17/09/06 Initial post
- 04/03/07 Added Indexing on view
