Learn About Views In SQL Server

In this article you will come to know about the 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.

    1. Single Table View
    2. Multiple Table View

What is Views?

 
As the name implies, VIEWS. SQL SELECT query is created only for viewing/display purposes. Views is a virtual table only used for viewing purpose. Views is created with the help of individual table or joining of tables. In short, in view we can join the fields from one or more database tables.
 
You can create multiple views of one table and one view with multiple tables.
 
View gives you the  power to decide what column(s) / fields(s) display or not. We can easily maintain and display the column(s) as per privileges of user.
 

Why should we use views?

 
Views allow administrator and user to only view the records of tables. Insert, Update and Delete functionality restriction can be implemented with views very easily; that's why we should use VIEW.
 
By the way Single Table View can be used to  Insert, Update and Delete the records. But multiple table VIEW cannot Update, Insert and Delete the records.
 

What is System Views?

 
Those views attached with system database and provide a detailed view of System Database called System Views.
 
Two types of System Views,
  • Information Schema
  • Catalog View

What is User Defined Views?

 
The View created by a user for specific tasksare  called User Defined Views.
 
Mostly user defined views are created on user defined database not on system database.
 

Syntax of User Views

 
Single Table View
  1. CREATE VIEW <view_name> AS    
  2. SELECT <column1>, <column2>. . .  
  3. FROM <table>   
  4. WHERE conditions;   
MultiTable View
  1. CREATE VIEW <view_name> AS    
  2. SELECT <column1>, <column2>. . .  
  3. FROM <tableJoin <Table>  
  4. WHERE conditions;   

Step by Step View Creation

 
Create Table
  1. ---tblEmployees Create Script  
  2. USE [MbkTest]  
  3. GO  
  4.   
  5. /****** Object:  Table [dbo].[tblEmployees]    Script Date: 26-Dec-19 12:07:27 PM ******/  
  6. SET ANSI_NULLS ON  
  7. GO  
  8.   
  9. SET QUOTED_IDENTIFIER ON  
  10. GO  
  11.   
  12. SET ANSI_PADDING ON  
  13. GO  
  14.   
  15. CREATE TABLE [dbo].[tblEmployees](  
  16.     [EmployeeID] [int] IDENTITY(1,1) NOT NULL,  
  17.     [PhoneNumber] [varchar](50) NULL,  
  18.     [SkillID] [intNULL,  
  19.     [YearsExperience] [intNULL,  
  20.     [EmployeeName] [nvarchar](50) NULL,  
  21. PRIMARY KEY CLUSTERED   
  22. (  
  23.     [EmployeeID] ASC  
  24. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  25. ON [PRIMARY]  
  26.   
  27. GO  
  28.   
  29. SET ANSI_PADDING OFF  
  30. GO  
  31.   
  32. ----tblSkills Create script  
  33. /****** Object:  Table [dbo].[tblSkills]    Script Date: 26-Dec-19 3:54:52 PM ******/  
  34. SET ANSI_NULLS ON  
  35. GO  
  36. SET QUOTED_IDENTIFIER ON  
  37. GO  
  38. SET ANSI_PADDING ON  
  39. GO  
  40. CREATE TABLE [dbo].[tblSkills](  
  41.     [SkillID] [int] IDENTITY(1,1) NOT NULL,  
  42.     [Title] [varchar](50) NULL,  
  43. PRIMARY KEY CLUSTERED   
  44. (  
  45.     [SkillID] ASC  
  46. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  47. ON [PRIMARY]  
  48.   
  49. GO  
  50. SET ANSI_PADDING OFF  
  51. GO  
Sample Datas
  1. ---tblEmployees Sample Records  
  2. GO  
  3. SET IDENTITY_INSERT [dbo].[tblEmployees] ON   
  4.   
  5. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (1, N'9869569634', 2, 11, N'Suhana Kalla')  
  6. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (2, N'9869166077', 8, 14, N'Ashish Kalla')  
  7. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (3, N'9869569634', 1, 24, N'Manoj Kalla')  
  8. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (4, N'9969359746', 6, 20, N'Nirupama Kalla')  
  9. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (5, N'9869166012', 7, 28, N'Rajesh Bohra')  
  10. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (6, N'9261166012', 5, 18, N'Murli Vyas')  
  11. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (7, N'9161569634', 2, 5, N'Magan Shukla')  
  12. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (8, N'9219166077', 4, 7, N'Jagat Pratap')  
  13. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (9, N'9459569634', 6, 10, N'Suresh Kamalkar')  
  14. INSERT [dbo].[tblEmployees] ([EmployeeID], [PhoneNumber], [SkillID], [YearsExperience], [EmployeeName]) VALUES (10, N'9687359746', 8, 2, N'Hari Vidhan')  
  15.   
  16. SET IDENTITY_INSERT [dbo].[tblEmployees] OFF  
  17.   
  18. ---tblSkills Sample Records  
  19. SET IDENTITY_INSERT [dbo].[tblSkills] ON   
  20. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (1, N'Visual Foxpro')  
  21. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (2, N'C#')  
  22. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (3, N'VB.NET')  
  23. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (4, N'Delphi')  
  24. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (5, N'Java')  
  25. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (6, N'Power Builder')  
  26. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (7, N'COBOL')  
  27. INSERT [dbo].[tblSkills] ([SkillID], [Title]) VALUES (8, N'Python')  
  28. SET IDENTITY_INSERT [dbo].[tblSkills] OFF  

Creating Single Table View

 
You can see in view we had used a single table called tblEmployees and only three columns are shown in output. Columns are EmployeeID, EmployeeName, and YearsExperience.
 
VIEW COMMAND
  1. CREATE VIEW YearExperienceMoreThan5YearsLess10Years  
  2. AS  
  3. SELECT EmployeeID, EmployeeName,YearsExperience  
  4. FROM tblEmployees a  
  5. WHERE a.YearsExperience >= 5 and a.YearsExperience <= 9  
Views In SQL Server
 
Views In SQL Server 
 
Refresh your database
 
Views In SQL Server 
 
Output - Testing View
 
You can see all records of tblEmployees :
 
Command
 
select * from tblEmployees
 
Views In SQL Server 
 
Now we execute/run our VIEW called,
 
Command
 
select * from [dbo].[YearExperienceMoreThan5YearsLess10Years]
 
Views In SQL Server 
 

Creating Multiple Table View

 
You can see in view we had used two tables,
  • tblEmployees
  • tblSkills
We had shown the following columns,
  • EmployeeID
  • EmployeeName
  • YearsExperience
  • Skills
VIEW COMMAND
  1. CREATE VIEW EmployeeWithSkillDetail  
  2. AS  
  3. SELECT a.EmployeeID, a.EmployeeName,a.YearsExperience,Skill = b.Title  
  4. FROM tblEmployees a   
  5. INNER JOIN tblSkills b  
  6. ON a.SkillID = b.SkillID  
Views In SQL Server 
 
Refresh Database and see view is created successfully,
 
Views In SQL Server 
 
OUTPUT
 
Now we execute/run our VIEW called,
 
Command  
 
select * from [dbo].[EmployeeWithSkillDetail]
 
Views In SQL Server 
 
Happy Coding. .