Views Introduction

You can use a view to save almost any SELECT statement as a separate database object. This SELECT statement enables you to create a result set that you can use just as you would any table. In a sense, you can think of a view as a "virtual" table. Views do not actually contain data-they simply consist of SELECT statements for extracting data from the actual tables in your database.

The tables on which you base a view are referred to as base tables. You can use a view to create a subset of a base table by selecting only some of its columns, or you can use a view to display columns from multiple base tables by using a join statement.



One of the best advantages of views is that you can give your server's users permissions only to the view itself and not the underlying data. Thus, a view provides you with additional security. You can also use views to enable users to see some but not all columns in a table-so if a table contains a column with sensitive information, you can use a view to restrict users from seeing that column. For example, if you have an employee table that contains employee names, addresses, and salaries, you can create a view to enable users to see the employee names and addresses, but not salaries.

You can also use views as a way to hide a complex database design. If you have normalized the design of your database such that data is spread out over multiple tables, it can be difficult for users to learn how to retrieve data from those tables. By using views, you can relieve users from having to learn how to write SQL statements to join the tables.



You create a view by using the CREATE VIEW Transact-SQL statement. You can include a total of 1,024 columns in a view. You cannot combine the CREATE VIEW with other SQL statements in the same batch. If you want to use other statements (such as USE database) with the CREATE VIEW statement, you must follow those statements with the GO keyword.

Use the following syntax to create a view:

USE database GO

CREATE VIEW view_name AS SELECT column_list

FROM table_name

Replace view_name with the name you want to assign to the view. You should come up with a naming convention for your views that makes it easier for you to differentiate between tables and views. For example, you might try using "view" as part of all of your view names. Replace column_list with the list of columns you want to include in the view, and table_name with the name of the table on which you want to base the view.

y

If you want to create a view that consists only of each customer's name and phone number, use the following syntax:

USE movies GO

CREATE VIEW dbo.CustView AS SELECT lname, fname, phone FROM customer

You can optionally specify a list of column names so that SQL Server will use these names for the columns in the view instead of the column names from the table in the SELECT portion of the statement. For example, in the following query, the (lname, fname) clause assigns these names to the columns in the view instead of the names au_lname, au_fname:

USE pubs GO

CREATE VIEW dbo.PracticeView (lname, fname)

AS

SELECT au_lname, au_fname FROM authors



You cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses in the SELECT statement you use to create a view. In addition, you cannot use the SELECT INTO keywords.

Your view cannot refer to temporary tables. For example, the following SQL statement is invalid:

CREATE VIEW dbo.TestView AS SELECT col1, col2

FROM #temp_table



If your users have permissions to the database in which you create the view, they will inherit permissions to the view itself. However, if your users do not inherit permissions to the view, you must assign them permissions or they will not be able to access the view. You do not have to give users permissions to the base tables on which you create a view-you just have to give users permissions to the view itself provided you are both the owner of the table and the view.



The views that you create depend on the base tables (or other views). SQL Server refers to objects that depend on other objects as dependent. Objects can have either the same or different owners. If the same owner owns both the view and the table, that owner (typically you) needs only to assign users permissions to the view. Likewise, when users access your view, SQL Server need check users permissions only for that view.

If you (or another user with sufficient permissions) create a view based on a table for which you are not the owner, SQL Server considers the ownership chain to be "broken." Each object's owner can change users' permissions; thus, SQL Server must check users' permissions for the view and all objects on which the view depends. Checking users' permissions for each object hurts your server's performance. Microsoft recommends that you do not break the ownership chain (meaning create views with different owners from the base tables) in order to avoid degrading the performance of your server.

To avoid breaking the ownership chain, you should explicitly specify the owner of the view when you create it. You should typically make the database owner (dbo) user the owner of all views along with all of the other objects in a database.

You make the dbo user the owner of a view by using the following syntax:

CREATE VIEW dbo.view_name AS SELECT column_list

FROM table_name



SQL Server enables you to create a view based on another view (this is also called a nested view). However, nested views can be much more difficult to troubleshoot because you must search through multiple view definitions to find a problem. For this reason, Microsoft recommends that you create separate views instead.



You can create a view based on joined tables by using a table join as part of your SELECT statement. You can use the following syntax to create a view based on a table join:

CREATE VIEW view_name (column_list)

AS

SELECT columns

FROM table1 JOIN table2 ON join_condition

y

If you want to create a view that contains the title of each book in the pubs database, along with the author's royalty percentage for that book, you could use the following query:

USE pubs GO

CREATE VIEW dbo.TitleRoyaltyView AS

SELECT t.title, r.royalty

FROM titles AS t JOIN roysched AS r ON t.title_id JOIN r.title_id

You should base views only on inner joins, not outer joins. While SQL Server enables you to specify an outer join in the SELECT statement, you will get unpredictable results.

y

SQL Server frequently returns null values for the inner table in the outer join.

Steps to create views from joined tables………..

Step 1:

Step 2:

Step 3:

Step 4:

Step 5:

Step 6:



You can use the following system views to display information about a database's views.

System view Based on system