Views in Sql Server

Definition:

A view is a virtual table created by a stored SQL statement that can span multiple tables. Views can be used as a method of security within your database, and they provide a simpler front end to a user querying the data.

A view is a virtual table that consists of columns from one or more tables. a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables. Once you have defined a view, you can reference it like any other table in a database.

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Views ensure the security of data by restricting access to the following data:

  • Specific rows of the tables.
  • Specific columns of the tables.
  • Specific rows and columns of the tables.
  • Rows fetched by using joins.
  • Statistical summary of data in a given tables.
  • Subsets of another view or a subset of views and tables.

Some common examples of views are:

  • A subset of rows or columns of a base table.
  • A union of two or more tables.
  • A join of two or more tables.
  • A statistical summary of base tables.
  • A subset of another view, or some combination of views and base table.

i.Creating Views: 

A view can be created by using the CREATE VIEW statement.

Syntax

CREATE VIEW view_name
[(column_name [, column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

Where:

View_name specifies the name of the view and must follow the rules for identifiers.

Column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

Example

CREATE VIEW vwCustomer
AS
SELECT
CustomerId, Company Name, Phone
FROM Customers

 

ii. Getting Information on a View:

SQL Server stores information on the view in the following system tables:

· SYSOBJECTS — stores the name of the view.

· SYSCOLUMNS — stores the names of the columns defined in the view.

· SYSDEPENDS — stores information on the view dependencies.

· SYSCOMMENTS — stores the text of the view definition.

There are also certain system-stored procedures that help retrieve information on views. The sp_help system-stored procedure displays view-related information. It displays the view definition, provided the name of the view is given as its parameter.

Example

Sp_helptext vwCustomer

Displays the definition of the vwCustomer view

Note

If a view is created with the WITH ENCRYPTION option, it cannot view the sp_helptext system-stored procedure.

iii. Altering Views:

You can modify a view without dropping it. This ensures that the permission on the view is also not lost. You can modify a view without affecting its dependent objects, such as triggers and stored procedures. You modify a view using the ALTER VIEW statement.

Example

ALTER VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone, Fax
FROM Customers

iv. Dropping Views:

You can drop a view from a database by using the DROP VIEW statement. When a view is dropped, it has no effect on the underlying tables. Dropping a view removes its definition and all the permissions assigned to it. Furthermore, if you query any views that reference a dropped view, you receive an error message. However, dropping a table that references a view does not drop the view automatically. You must drop it explicitly.

Syntax

DROP VIEW view_name

Where:

View_name is the name of the view to be dropped.

You can drop multiple views with a single DROP VIEW statement. The names of the views that need to be dropped are separated by commas in the DROP VIEW statement.

v.. Renaming Views

You can rename a view without having to drop it. This ensures that the permissions on the view are not lost.

· A view can be renamed by using the sp_rename system stored procedure.

Syntax

Sp_rename old_viewname, new_viewname

Where:

Old_viewname is the view that needs to be renamed.

New_viewname is the new name of the view.

Example

Sp_rename vwCutomers vwCustomerDetails

Renames vwCutomers to vwCustomerDetails

vi. Manipulating Data Using Views:

You can modify data by using a view in only one of its base tables even though a view may be derived from multiple underlying tables. For example, a view vwNew that is derived from two tables, table and table2, can be used to modify either table or table2 in a single statement. A single data modification statement that affected both the underlying tables is not permitted.

vii. When to Use a View:

You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.

  • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  • To control access to rows and columns of data- a view can be a useful tool to provide both column and row level security for a database.
  • To aggregate data for performance- if you need to roll up or aggregate data from a set of tables.

viii. Modifying Data through a View:

You can modify data through a view in these ways:

  • Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.
  • Use updatable partitioned views that modify one or more member tables.

 

Advantages of views:

1. View the data without storing the data into the object.

2. Restricts the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4.  restricts the access of a table so that nobody can insert the rows into the table.

Disadvantages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive… it depends on the table objects.

3. It is an object, so it occupies space.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: