In SQL, Views are virtual tables that give a simplified and customized view of data from one or multiple database tables. They are similar to a table with rows and columns but do not store actual data.
This article will cover the different types of views in SQL Server with multiple examples. Also, we will learn about how to create, modify, update, and delete views in SQL Server.
Table of Contents
- 1 What is a view in SQL Server?
- 2 Advantages Of Using Views in SQL Server
- 3 Key Points: SQL Server Views
- 4 What are the types of views in SQL Server?
- 5 User-Defined Views in SQL
- 6 01. Simple View In SQL
- 7 02. Complex View In SQL
- 8 System Views
- 9 Renaming the View in SQL Server
- 10 Retrieving Information about a View
- 11 Alter View in SQL Server
- 12 Refresh a View in SQL Server
- 13 Encrypt a View in SQL Server
- 14 FAQs
- 15 Conclusion
What is a view in SQL Server?
SQL Server Views are virtual tables containing data from one or multiple tables. They don’t store any data or have a physical presence in the database. Despite this, we can still apply indexes and triggers to views.
The result of the SELECT statement is stored in the View and can be queried like a regular table. The data in the View is based on the information stored in the original tables. So, any changes to the View will reflect in the original tables.
Advantages Of Using Views in SQL Server
Here are several benefits to using views in SQL Server, such as:
- Simplifying Data Access: Views can simplify data accessing from complex or large tables. Users can find what they need by showing important columns and rows in the view without searching through a large and complicated table.
- Data Security: Using views helps to keep sensitive data safe by limiting who can access it. You can use view to show only essential columns and rows without exposing the actual table to the users.
- Improving Query Performance: Using views can make your queries run faster by organizing the data beforehand and making complicated queries easier. It helps you save time by not having to do calculations during the query and speeds up the overall performance.
Views in SQL Server provide a way to access data securely by letting users see the information in a view instead of the actual table columns.
Key Points: SQL Server Views
- A View is a virtual table that displays selected columns from one or more tables.
- It does not store actual data but only holds select query statements.
- We can create views with SQL select statements.
- A View is a logical entity, while a table is a physical entity.
- We can use views to provide a backward-compatible interface, simplify complex SQL queries, and protect base table data.
Syntax to create Views in the SQL server
The following is the syntax to create a view in the SQL server.
We can use the Views in SQL Server to provide row and column level security. We can allow users to access only the selected columns of the table instead of accessing the entire record in the table.
What are the types of views in SQL Server?
SQL Server has two types of views: system-defined and user-defined.
- System Defined Views
- Information Schema View
- Catalog View
- Dynamic Management View
- User DefinedView
- Simple View
- Complex View
User-Defined Views in SQL
User-defined views are views that users create. User-defined views allow you to simplify complex data structures and improve data security. They can be created using the CREATE VIEW statement and queried, updated, or deleted like any other table.
There are two types of user-defined views: simple and complex.
01. Simple View In SQL
In SQL, views created on a single table are called simple views.
Simple views support DML operations. If the base table contains a primary key and no empty columns, we can insert, update or delete records.
Example: Simple view in SQL
The following is an example of creating a simple view on the SQL server.
Let’s create a simple view with small records on the
-- Creating a new table "tblEmployee". CREATE TABLE tblEmployee ( [Id] INT PRIMARY KEY, [Name] NVARCHAR(30), [Gender] VARCHAR(10), [City] VARCHAR(50), [DeptId] INT ); --Inserting data into the "tblEmployee" table. INSERT INTO tblEmployee VALUES (101,'Shekh Ali','Male','Delhi', 1), (102,'Mark Adam','Male','New York', 1), (103,'Julie Folden','Female','Chicago', 2), (104,'Rohit','Male','Delhi', 2);
Creating a View in SQL
CREATE VIEW [SimpleViewOnTblEmployee] AS SELECT Id, Name, Gender, City, DeptId FROM tblEmployee;
In this example, we have created a user-defined view named [SimpleViewOnTblEmployee] that retrieves data from the tblEmployee table.
-- Show records from the simple view. SELECT * FROM [SimpleViewOnTblEmployee];
We can simply run the Select statement to see the data from the view:
|102||Mark Adam||Male||New York||1|
Inserting new records using Simple view in SQL
In a simple view, we can insert, update and delete records. The following query is to insert a new record in a simple view.
-- Insert record into Simple view INSERT INTO [SimpleViewOnTblEmployee] VALUES (106,'Roman','Male','New York', 1);
Let’s check the updated record from the table.
SELECT * FROM [SimpleViewOnTblEmployee];
|102||Mark Adam||Male||New York||1|
Updating records using simple view in SQL
The following update query will successfully update the record with id 104 from the view.
UPDATE [SimpleViewOnTblEmployee] SET Name='Daniel' WHERE Id = 104;
Let’s check the updated record.
SELECT * FROM [SimpleViewOnTblEmployee] ;
Deleting records using simple view in SQL
The following command will delete the record with the Id 106 from the base “tblEmployees” table.
Delete from [SimpleViewOnTblEmployee] where Id = 106 ;
We can simply run the following Select statement to see the updated records.
Select * from [SimpleViewOnTblEmployee] ;
In the image below, it is shown that the record with Id 106 has been successfully deleted.
|102||Mark Adam||Male||New York||1|
02. Complex View In SQL
A View created on more than one base table is known as a Complex View . It doesn’t allow to perform
DML operation. A complex view is also known as a non-updatable or static view.
The complex view can contain a
group by clause,
order by clause,
Aggregate Functions, or
-- Create a new [DEPARTMENT] table. CREATE TABLE DEPARTMENT ( [Id] [int] PRIMARY KEY NOT NULL, [DeptName] [nvarchar](50) NULL, ); -- INSERT RECORD INTO THE DEPARTMENT TABLE. INSERT INTO DEPARTMENT VALUES(1,'IT'),(2,'HR'),(3,'FINANCE'); -- Show record from the department table. SELECT * FROM DEPARTMENT;
Now create a complex view on the tables [tblEmployee] and [Department].
CREATE VIEW vw_tblEmployee_Department AS SELECT e.Id, e.Name,e.Gender,e.City,d.DeptName FROM tblEmployee e INNER JOIN Department d ON e.DeptId = d.Id;
We can simply run the below Select query to display the records from the complex view [vw_tblEmployee_Department].
SELECT * FROM vw_tblEmployee_Department;
|102||Mark Adam||Male||New York||IT|
Update Complex View in SQL
Note: We can update the existing records in a complex view but can’t insert the new ones. If a complex view is based on multiple tables, it might not allow updating the base table correctly. To update the complex view, instead of trigger can be used.
The following is the SQL query to update the complex view.
-- Query to update [City] in the in the complex view [vw_tblEmployee_Department]. UPDATE vw_tblEmployee_Department set City ='Mumbai' Where Id = 101;
Once we run the above query, we can see that the City is updated from “Delhi” to “Mumbai” for Id 101.
SQL Server has predefined views known as System Views stored in its Master database. These views serve as a model for any newly created databases and provide information about the system’s metadata, performance data, and objects. These views are not editable and are managed solely by SQL Server. Some commonly used system views include
The following is an example of how to use the
sys.databases system view to retrieve information about the databases in SQL Server:
SELECT name, create_date, state_desc FROM sys.databases;
This example will retrieve the name, creation date, and state of each database in the SQL Server.
The master database generally serves as the template database for all other user-defined databases on the SQL server. It contains almost 230 predefined views.
The following are three system-defined views:
- Information schema view
- Catalog view
- dynamic management view.
01. Information Schema Views In SQL Server
In SQL Server, approximately 21 different Information schema views are available. These views start with INFORMATION_SCHEMA as a prefix. They provide information about the database objects such as tables, columns, domains, check constraints, etc.
The below system-defined view will display the details of the columns in the tblEmployee table.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblEmployee'; -- OR SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblEmployee';
The following INFORMATION_SCHEMA.CHECK_CONSTRAINTS is used to return the information about the constraints of a table.
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME ='tblEmployee';
INFORMATION_SCHEMA.VIEWS will return all the views present in the current database.
SELECT * FROM INFORMATION_SCHEMA.VIEWS ;
02. What are Catalog Views in SQL Server?
Catalog views are a type of system view in SQL Server that provide access to database metadata.
They describe database objects such as tables, views, indexes, stored procedures, and other database elements.
Catalog views allow you to retrieve information about your database’s structure and the relationships between different database objects.
SQL Server provides several built-in catalog views that you can use to retrieve information about the database.
For example, the
sys.tables view can be used to get information about tables in the database while the
sys.indexes view can be used to get information about indexes in the database.
sys.objects catalog view returns rows of objects other than tables, such as stored procedures and views. Once a new table is created, the metadata for the table is returned in both
-- Catalog view example for tables: SELECT * FROM sys.tables; -- Catalog view example for database SELECT name, database_id, compatibility_level,* FROM sys.databases WHERE name LIKE 'master%';
03. What are Dynamic Management Views In SQL Server?
The Dynamic Management Views were introduced in SQL Server 2005. These Views provide the administrator information about the server state to diagnose problems and monitor the SQL server machine’s health and current state.
These views help the administrator to analyze database-related problems and tune the SQL server for optimal performance.
DMVs provide information about the runtime behavior of the database and its components, such as sessions, transactions, and performance statistics.
There are two types of Dynamic Management Views.
01. Server-Scoped Dynamic Management View: These types of views are stored only in the Master database.
02. Database Scoped Dynamic Management View: These types of views are stored in each database.
--This returns current session information SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name; --To see all SQL Server connections SELECT connection_id,session_id,client_net_address,auth_scheme FROM sys.dm_exec_connections ;
Renaming the View in SQL Server
The sp_rename system procedure can be used to change the name of a view in SQL Server.
The syntax for using the sp_rename command is as follows:
sp_rename OldViewName , NewViewName
Retrieving Information about a View
We can get all the details about a view using the
sp_helptext system stored procedure. Let’s take a look at an example.
Alter View in SQL Server
The schema or structure of a view in SQL Server can be altered. This means that columns can be added or removed, and conditions applied in the view can be modified.
Here’s an example of how you can alter a view in SQL Server using the
ALTER VIEW statement:
-- Creating a sample view CREATE VIEW SalesView AS SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Orders; -- Altering the view to include a new column ALTER VIEW SalesView AS SELECT OrderID, CustomerID, OrderDate, TotalAmount, Status FROM Orders;
Refresh a View in SQL Server
In SQL Server, you can refresh a view by executing the view definition or using the
sp_refreshview system stored procedure.
Executing the view definition is the simplest way to refresh a view. You can simply run a SELECT statement on the view, and it will retrieve the latest data from the underlying tables.
Here is a sample example of using the
sp_refreshview system stored procedure to refresh a view:
EXEC sp_refreshview 'myView';
sp_refreshview procedure takes the name of the view as an argument and updates the data in the view accordingly.
Encrypt a View in SQL Server
Encrypting a view in SQL Server is a way to secure sensitive data and prevent unauthorized access or modification of the view. By doing so, the view data will not be accessible through the
SP_HELPTEXT stored procedure.
In SQL Server, you can encrypt a view using the WITH ENCRYPTION option when creating or altering the view. Here is the syntax for creating an encrypted view:
CREATE VIEW view_name WITH ENCRYPTION AS SELECT column1, column2, ... FROM table_name WHERE conditions;
Q: What is a view in SQL Server?
A view in SQL Server is a virtual table that provides a specific data view from one or more tables in the database. Views allow you to simplify complex data structures, enforce data consistency, and improve data security.
Q: How can you alter a SQL view?
The following is the syntax to alter an existing view.
ALTER VIEW ViewName
SELECT Column1, Column2, Column3 …
Q: How do you drop a view from a database?
A view in the SQL server can be deleted by using the DROP VIEW statement.
SQL DROP VIEW Syntax:
DROP VIEW view_name;
Q: What are indexed views in SQL Server?
Indexed views in SQL Server are user-defined views optimized for performance using indexes. Indexed views can significantly improve query performance, especially for complex queries.
Q: Can you update data in a SQL Server View?
Yes, you can update data in a SQL Server view, but some restrictions exist. For example, you can only update a view if it is based on a single table and its columns are updatable.
This article delves into the fundamentals of SQL Server views, explaining what they are, how they operate, and their significance. We learned about various views, including system, catalog, and dynamic management views. Additionally, we explore modifying and encrypting views and creating views with specific conditions.
References w3schools: SQL Views
You might also like:
- Having VS Where Clause in SQL
- Group by Month in SQL
- Delete vs Truncate vs Drop in SQL Server
- UNION vs UNION ALL in SQL SERVER
- Different ways to delete duplicate rows in SQL Server
- Types of Joins in SQL Server
- Function vs Stored procedure in SQL Server
- Stored Procedure in SQL Server With Examples
- Create, Alter, and Drop Database In SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
- Primary Key Constraint In SQL Server
- View In SQL | Types Of Views In SQL Server
- SQL Server Trigger Update, Insert, Delete Examples
- SQL pivot tables: Understanding pivot tables in SQL Server
Don’t keep this post to yourself, share it with your friends and let us know what you think in the comments section.
- C# Program to Convert Binary to Decimal with Examples - December 4, 2023
- C# Program to Print Multiplication Table of a Given Number - November 30, 2023
- Palindrome program in C# with examples - November 28, 2023