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

IntroductionMany 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 assemblediffcult 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 alwaysit contains a sequence of queries fired dynamically to fetched data from different tables. Virtual table shows only those data which are already mentionedin the query during the VIEW creation.

Digramatic representation of VIEWFollowing snap will clear the idea about VIEW

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

Syntaxhere is syntax

CREATE VIEW viewnameASselect statement

VIEW always have SELECT statement in it.

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

now i am going to create VIEW with only two columns

CREATE VIEW DemoViewAsSELECT Name, Age FROM EMP

The above code create a VIEW named DemoView.

Get result from VIEW

Select * from DemoView

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

Drop VIEWwe 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 Views2. User Defined Views

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

let's take a deep viewSystem ViewSystem Views are predefined views that are already present in Master database. There are in all 230 views available. we can see the system view fromSQL 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 nameetc.

select * from INFORMATION_SCHEMA.columnswhere table_name = 'emp'

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

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

select * from sys.tables

sample output is as follows

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

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

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

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