VIEW in SQL Server : A short trip

In this article, i have explain you about the VIEWs in SQL Server, it's creation, types of views exist in sql and delete existing view. Basically view are virtual table containing columns from different tables.

View in SQL Server

Index

Introduction
What is VIEW
Syntax
Illustration
Get result from VIEW
Drop VIEW
Types of VIEW
Summing up

Introduction
Many time in business scenarios we need to fetch data from two or more tables, in that case it is always better to keep all required columns in single table and
fetch from single point. So, for that scenario SQL introduce VIEW.
In this article i am going to explain you about the VIEW in SQL server. VIEW is important element of SQL by which we can save time to assemble
diffcult and complex queries. This article will help you to use VIEW. let's see the step wise aspect.

What is VIEW

VIEW is basically a virtual table containing combination of rows and columns. VIEW contains data from one or more table, VIEW does not contains data always
it contains a sequence of queries fired dynamically to fetched data from different tables. Virtual table shows only those data which are already mentioned
in the query during the VIEW creation.

Digramatic representation of VIEW
Following snap will clear the idea about VIEW

DiagramaticView

in above snap we have create VIEW with the help of TableA and TableB, in which we fetch Col_A1, Col_A2 from TableA and Col_B3 from TableB

Syntax
here is syntax


CREATE VIEW  viewname
AS
select statement

VIEW always have SELECT statement in it.

Illustration
here we will see how to create a view with the help of existing tables
suppose i have a table EMP with 3 columns Name, Age, Sex in it and it has some records

Table_EMP

now i am going to create VIEW with only two columns


CREATE VIEW DemoView
As
SELECT Name, Age FROM EMP

Create_View

The above code create a VIEW named DemoView.

View_DemoView

Get result from VIEW


Select * from DemoView

The above query will return us a result from Demoview
have a look at the output

Select_View

Drop VIEW
we can delete a view using DROP command, here is example


DROP VIEW DemoView

Types of VIEW

There are two different types of Views:
1. System Views
2. User Defined Views

further system views are divided in to following groups
1. Information View
2. Catalog View
3. Dynamic Management View (DMV)

let's take a deep view
System View
System Views are predefined views that are already present in Master database. There are in all 230 views available. we can see the system view from
SQL Server Management Studio --> Master database --> View --> System View.

each system view group has it's different meaning and purpose.

Information View:
This is most important group of system views that provides us physical information of tables with columns.
The name of the system view is "INFORMATION_SCHEMA". it's store the information of all databases with all columns.
This view provide us all information about columns like it's name, position sequence, NULLable, data type, character length, numeric precision, collation name
etc.


select * from INFORMATION_SCHEMA.columns
where table_name = 'emp'

Information_schema

---------------------------------------

Catalog view:
This group of view used to describe the database information
here is example


select * from sys.tables

sample output is as follows

Catalog_view

this view gives us all physical information about databases, it's creation datetime, collation name, online/offline state and many more.

---------------------------------------

Dynamic Management View:
This view is Commence in SQL Server 2005, basically this view help for administration of database server.
it will return us many important information like recent connect time to sql, client IP address which connects to database server,
last session id for sql, last_read and last_write on database and most recent sql handle etc.
here is syntax


SELECT * FROM sys.dm_exec_connections

Dynamic_view

---------------------------------------

User Defined Views
This the simple and customized view. we have already go through it's syntax and example.

Summing up
view is a virtual table which create from one or more tables. It may contain many columns from different tables.

Suggestion and Queries always welcome

Thanks
Koolprasad2003

VIEW in SQL Server : A short trip