About Views in SQL Server

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME

C#Corner dbo Authors AuthordId

C#Corner dbo Authors AuthorName

C#Corner dbo Authors Article

C#Corner dbo Authors AuthorRank

Catalog view

Catalog Views are used to show database self-describing information.

Catalog views are also used to return information that is used by the SQL Database Engine like objects, logins permissions and so on.

Example:

Dynamic Management View (DMV)

DMVs are introduced in SQL Server 2005.

DMVs gives the database administration information about the current state of SQL Server machine on various aspects.

DMVs are easier to detect the health of SQL Server using these views.

DMVs replace many of the DBCC Commands.

All Dynamic Management Views (DMVs) exist in the sys schema and follow this naming convention dm_*.

There are two types of DMVs.

1. Server-scoped DMV

Server-scoped DMVs are Stored in Master Database.

Server-scoped DMVs are used to for the state of an entire SQL Server instance.

Server-scoped DMVs require VIEW SERVER STATE PERMISSION on the server.

2. Database-scoped DMV

Database-scoped DMVs are stored specific to each database.

Database-scoped DMVs require DATABASE STATE PERMISSION on the database.

The following are some of the Dynamic Management Views:

Sys.dm_exec_Cached_plans: Returns the information about query Execution Plans that are cached by SQL-SERVER for faster query execution.

Sys.dm_exec_Query_plan: Returns the show plan in XML format for a T-SQL batch.

Sys.dm_exec_Query_stats: Returns aggregate performance statistics for Cached Query Plans.

Sys.dm_exec_requests: Returns information about each request that is executing within SQL Server.

User Defined Views

These are the views that are defined by the user as per the their requirements.

Simple view

A simple view is one that can be addressed by DML statements as well as SELECT. As might be expected, simple views are based on relatively simple SELECT statements.

It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted data in the table the view was created with. It should also be noted that as data in the original table data changes.

A simple view can be created from a single table.

A simple view does not contain functions.

A simple view does not contain a group of data.

Complex View

We use Complex Views when we want to display data from two or more tables, using a group cluase or a grouping of aggregate functions.

A Complex View can be created from one or more table.

A Complex View contains functions.

A Complex View contains a group of data,

Creating Views

We can create views in 2 ways.

1. Through Query Designer.
2. Through Database

Through Query Designer

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

Syntax:

Create view view_Name

As

....Ur Query.....

Example:

Create view Authors Info

As

Select AuthorName,Article,Rank from Authors

Using SQL Server Management Studio

To create a view using the Query and View Designers:

1. In Object Explorer, expand the database where you want to create your new view.
2. Right-click the Views folder, then click New View.
3. In the Add Table dialog box, select the table that you want to include in your new view from one of the following tabs: Tables, Views, Functions, and Synonyms.
4. Click Add, then click Close.
5. In the Query Design Pane, select the columns or other elements to include in the new view.
6. In the Criteria Pane, select additional sort or filter criteria for the columns.
7. On the File menu, click Save view name.
8. In the Choose Name dialog box, enter a name for the new view and click OK.

Summary

This article intended to describe the Views in SQL Server with examples. I hope after reading this article your SQL views concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.