Views in SQL Server
In this article you will come to know about followings things:
1. What is Views?
2. Why we should use views?
3. What is System Views?
4. What is User Defined Views?
5. Syntax of User Views
6. Step by Step View Creation & Execution.
Single Table View
Multiple Table View
What is Views?
As name define itself VIEWS. SQL SELECT query created only for view purpose that called VIEWS. Views is virtual table only use for view purpose. Views is created with the help of individual table or joining of tables. In short in view we can club the fields from one or more database tables.
You can create multiple view of one table and one view with multiple tables.
View give you power what column(s) / fields(s) display or not.
Why we should use views?
View allow administrator and user to only view the records of tables. Insert, Update and Delete functionality restriction can be implement with views very easily thats why we should use VIEW.
By the way Single Table View can be Insert, Update and Delete the records. But multiple table VIEW can not Update, Insert and Delete the records.
[Contact for Personalized Hand Holding training for Asp.Net MVC / Asp.Net Web Forms , C#, Sql Server, Jquery, Java, Angular Training in Kandivali, Angular Classes in Malad, React Classes in Malad, React Classes in Kandivali]
What is System Views?
Those view attached with system database and provide detailed view of System Database called System Views.
Two types of System Views:
Information SchemaCatalog View
What is User Defined Views?
Which View created by user for specific task and reason in mind those called User Defined Views.
Mostly user defined views created on user defined database not on system database.
Syntax of User Views:
Single Table View
CREATE VIEW <view_name> AS
SELECT <column1>, <column2>. . .
FROM <table>
WHERE conditions;
MultiTable View
CREATE VIEW <view_name> AS
SELECT <column1>, <column2>. . .
FROM <table> Join <Table>
WHERE conditions;
Step by Step View Creation :
Create Table:
---tblEmployees Create Script
USE [MbkTest]
GO
/****** Object: Table [dbo].[tblEmployees] Script Date: 26-Dec-19 12:07:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[PhoneNumber] [varchar](50) NULL,
[SkillID] [int] NULL,
[YearsExperience] [int] NULL,
[EmployeeName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
----tblSkills Create script
/****** Object: Table [dbo].[tblSkills] Script Date: 26-Dec-19 3:54:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSkills](
[SkillID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[SkillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Sample Datas:
---tblEmployees Sample Records
GO
SET IDENTITY_INSERT [dbo].[tblEmployees] ON
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (1, N'9869569634', 2, 11, N'Suhana Kalla')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (2, N'9869166077', 8, 14, N'Ashish Kalla')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (3, N'9869569634', 1, 24, N'Manoj Kalla')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (4, N'9969359746', 6, 20, N'Nirupama Kalla')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (5, N'9869166012', 7, 28, N'Rajesh Bohra')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (6, N'9261166012', 5, 18, N'Murli Vyas')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (7, N'9161569634', 2, 5, N'Magan Shukla')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (8, N'9219166077', 4, 7, N'Jagat Pratap')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (9, N'9459569634', 6, 10, N'Suresh Kamalkar')
INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (10, N'9687359746', 8, 2, N'Hari Vidhan')
SET IDENTITY_INSERT [dbo].[tblEmployees] OFF
---tblSkills Sample Records
SET IDENTITY_INSERT [dbo].[tblSkills] ON
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (1, N'Visual Foxpro')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (2, N'C#')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (3, N'VB.NET')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (4, N'Delphi')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (5, N'Java')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (6, N'Power Builder')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (7, N'COBOL')
INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (8, N'Python')
SET IDENTITY_INSERT [dbo].[tblSkills] OFF
[Angular Training in Borivali, Angular Classes in Pune, React Classes in Jodhpur, React Classes in Jodhpur, Angular Tutor in Jaipur, Angular Tutor in Pali, Angular training in Bikaner, Angular Training in Navsari, Angular Training in Surat, React JS Training in Bharuch]
Creating Single Table View:
You can see in view we had used single table called tblEmployees and only three columns shown in output. Columns are EmployeeID, EmployeeName, and YearsExperience.
VIEW COMMAND:
CREATE VIEW YearExperienceMoreThan5YearsLess10Years
AS
SELECT EmployeeID, EmployeeName,YearsExperience
FROM tblEmployees a
WHERE a.YearsExperience >= 5 and a.YearsExperience <= 9
 |
| Angular Classes Kandivali, Anguarl Training Institute in Malad, Asp.Net Tutor in Malad, Asp.Net Kandivali |
Refresh your database :
 |
| Visual Foxpro Training in Mumbai, Foxpro Programmer in Hydrabad |
Output: Testing View
You can see all records of tblEmployees :
Command : select * from tblEmployees
 |
| Angular Classes Goregoan, Anguarl Training Institute in Andheri, Asp.Net Tutor in Jogeshwari, Asp.Net Vileparle |
Now we execute/run our VIEW called:
Command : select * from [dbo].[YearExperienceMoreThan5YearsLess10Years]
 |
| YCMOU BCA Training Course, BCA Coaching Classes, BCA Java Training Institute |
Creating Multiple Table View:
You can see in view we had used two tables:
tblEmployees
tblSkills
We had shown following columns:
EmployeeID
EmployeeName
YearsExperience
Skills
VIEW COMMAND:
CREATE VIEW EmployeeWithSkillDetail
AS
SELECT a.EmployeeID, a.EmployeeName,a.YearsExperience,Skill = b.Title
FROM tblEmployees a
INNER JOIN tblSkills b
ON a.SkillID = b.SkillID
 |
| BCA Android Training Institute, JAVA Course |
Refresh Database and see view created successfully:
 |
| Asp.Net Training Institute in Dahisar, Asp.net Classes in Malad East, .Net Classes in Palghar |
[Angular Training in Vapi, Angular Classes in Bhopal, React Classes in Ajmer, React Classes in Vap, Angular Tutor in Jaipur, Angular Tutor in Nashik, Angular training in Mathura, Angular Training in Bharatpur, Angular Training in Near Surat, React JS Training in Near Baroda]
OUTPUT:
Now we execute/run our VIEW called:
Command : select * from [dbo].[EmployeeWithSkillDetail]
 |
| Asp.Net Classes in Mira Road, Asp.Net training Institute in Bhayandar |
Happy Coding. . .
