Views in SQL Server with [Examples]

Last updated on February 14th, 2023 at 07:39 am

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.

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.

1. System Defined Views
   • Information Schema View
   • Catalog View
   • Dynamic Management View
2. 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 tblEmployee table.

-- 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:

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];

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.

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 join conditions.

-- 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;

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.

System Views

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 sys.databases, sys.tables, and sys.columns.

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 ServerS nII LSn n

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'; 

The following 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.

The 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 sys.objects and sys.tables views.

Example:

-- 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.

sp_helptext vw_tblEmployee_Department;

Output:

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';

The 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;

FAQs

Conclusion

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.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024