Different Types of VIEW

There are two different types of VIEW

* System View
o Information Schema View
o Catalog View
o Dynamic Management View(DMV)
* User Define View
o Simple View
o Complex View


In SQL Server all system view are divided into different schema. These are used for the security container of SQL Server database. We can categorized system view in following way,

* Information Schema View
* Catalog View
* Dynamic Management View (DMV)

Now all above category are itself a huge topic, So I will not going to Details of It. Lets have a look into the over view of those view type

Information View

These are the one of the most important system grouped view. There are twenty different schema views in this group. This are used for displaying most physical information of a database, such as table, columns. Naming Conation of this type of views are INFORMATION_SCHEMA.[View Name] . From the System View Image we can get the few name of Information Schema View.

Lets see it with one Example,

I have create on Database named, ViewDemo. It having on table called EmpInfo and below diagram showing you the design of the table,
Now, if we want to know the details information columns of table Empinfo using View we have to run the following query,



SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='EmpInfo'



Similarly we can use all other Schema View to read Database information.

Catalog View

This type of view are introduced in SQL Server 2005. catalog view are categorized in different group also. These are used to show the database self describing information.

As for example,
Collapse

select * from sys.tables



Dynamic Management View

This is newly introduced in SQL Server 2005.This Views gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMV

1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database

As for example if we want to check the all SQL Server connection, we have to use following query,
Collapse

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

http://www.codeproject.com/KB/database/View.aspx

http://www.c-sharpcorner.com/UploadFile/skumaar_mca/viewsinsqlserver200510132009103348AM/viewsinsqlserver2005.aspx