Post
Ask Question

About Views in SQL Server

Introduction

This article intends to describe Views in SQL Server. I hope this article will help you.

What a View is

  • A view is a virtual table in the database whose contents are defined by a query.
  • A view appears just like a real table, with a set of named columns and rows of data. Unlike a real table, a view does not exist in the database as a stored set of data values. Instead, the rows and columns of data are generated from the query results defined by the View.

Types of Views in SQL Server

  1. System Views

    • Information Schema View.

    • Catalog view

    • Dynamic Management View (DMV)

  2. User Defined Views

    • Simple View

    • Complex View
    1. create table Authors  
    2. (  
    3. AuthordId int,  
    4. AuthorName varchar(Max),  
    5. Article varchar (Max),  
    6. AuthorRank int  
    7. )  
    8. -------Inserting Data into Authors Table----  
    9. Insert into Authors values (1,'Mahesh Chand','C# fundamentals',1)  
    10. Insert into Authors values (2,'PraveenKumar','Wpf',20)  
    11. Insert into Authors values (3,'Dhananjaykumar','windowsApplication',3)  
    12. Insert into Authors values (4,'PinalDeve','SqlTrace',4)  
    13. Insert into Authors values (5,'Abhinav','oops',2)  
    14. Insert into Authors values (6,'Abhijit','WCF',5)  
    15. Insert into Authors values (7,'Amit','DatabaseMirroring',7)  
    16. Insert into Authors values (8,'Karthik','ssis',8)  
    17. Insert into Authors values (9,'Divya','WebApi',9) 
    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

System Views

System Views are predefined Views that already exist in the Master database of SQL Server. These System Views are used as template Views for all newly created databases. These system Views will be automatically created for any user defined database.

http://www.dotnet-tricks.com/Content/images/sqlserver/systemviews.png

Information Schema View

The Information Schema Views are used to display information of a database, such as tables and columns. In SQL Server there are nearly twenty different Information Schema Views.

Example: To determine the complete information of an Authors table using an Information Schema View:

  1. ---To see the detail information of Authors Table  
  2.   
  3. elect * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Authors' 

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:

  1. --For list of all Views in a Database  
  2. select * from sys.all_views  
  3. ---For list of tables in a database  
  4. select * from sys.tables 

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.


Similar Articles