A view is a virtual table created from the reduced and unified result set of a SQL query. It is helpful for strengthening maintainability, strengthening security, and streamlining data access.
Views dynamically retrieve data from underlying tables in accordance with the query definition; they do not retain data on their own. It is the finest illustration of SQL Syntax's encapsulation and abstraction features.
CREATE VIEW view_name AS
SELECT column1, column2, column3 ...
FROM table_name
WHERE condition;
Example(MS SQL server)
We need to create a view that returns student ID, student name, class name, and enrollment date from 3 tables having student details, Class details, and Enrollment.
Table Creation, added tbl_ at the start of the table name to easily identify it as a table.
-- Students Table
CREATE TABLE tbl_Students (
StudentID INT PRIMARY KEY,
Name NVARCHAR(50),
ClassID INT
);
-- Classes Table
CREATE TABLE tbl_Classes (
ClassID INT PRIMARY KEY,
ClassName NVARCHAR(50)
);
-- Enrollments Table
CREATE TABLE tbl_Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
ClassID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES tbl_Students(StudentID),
FOREIGN KEY (ClassID) REFERENCES tbl_Classes(ClassID)
);
Sample Data Insertion
-- Insert Data into Students Table
INSERT INTO tbl_Students (StudentID, Name, ClassID)
VALUES
(1, 'Peter', 101),
(2, 'Scott', 102),
(3, 'Tim', 101);
-- Insert Data into Classes Table
INSERT INTO tbl_Classes (ClassID, ClassName)
VALUES
(101, 'Mathematics'),
(102, 'Science'),
(103, 'History');
-- Insert Data into Enrollments Table
INSERT INTO tbl_Enrollments (EnrollmentID, StudentID, ClassID, EnrollmentDate)
VALUES
(1, 1, 101, '2024-01-15'),
(2, 2, 102, '2024-01-16'),
(3, 3, 101, '2024-01-17');
Create a view named VW_StudentClassEnrollment, added VW at the starting of the view name to easily identify it as a View, not a table.
CREATE VIEW VW_StudentClassEnrollment AS
SELECT
s.StudentID,
s.Name AS StudentName,
c.ClassName,
e.EnrollmentDate
FROM
tbl_Students s
INNER JOIN
tbl_Enrollments e ON s.StudentID = e.StudentID
INNER JOIN
tbl_Classes c ON e.ClassID = c.ClassID;
-- Get data from the view
SELECT *
FROM VW_StudentClassEnrollment;
Result
| StudentID |
StudentName |
ClassName |
EnrollmentDate |
| 1 |
Peter
|
Mathematics |
15-01-2024 |
| 2 |
Scott |
Science |
16-01-2024 |
| 3 |
Tim |
Mathematics |
17-01-2024 |
Drop View
We can drop a view by using the command Drop view view_name;
Benefits of using view
- Simplification: Views simplify complex SQL queries. Instead of writing a complex join or aggregation query multiple times, you define it once in a View and use the View in your queries.
- Security: Views can restrict access to specific columns or rows in a table. You can grant users access to the View without giving them direct access to the underlying tables.
- Example.
GRANT SELECT ON VW_StudentClassEnrollment TO some_user;
Maintainability: Views centralize the logic for complex queries. If the underlying tables change, you only need to update the View definition rather than every instance of the query in your application.
Abstraction: Views abstract the underlying table schema from users. They provide a simplified and consistent interface to the data.
Encapsulation: View restricts the direct access to the table user to create a view. If the query logic needs to change, it is updated in the View definition without modifying every instance where the query is used.
Advanced features
- Updatable Views: Some Views can be updated directly if they meet certain criteria, such as having a one-to-one relationship with the underlying table and not containing any aggregate functions.
- Indexed Views: In some databases, you can create indexed views to improve performance. Indexed views materialize the result set and store it physically, providing faster query performance.
MS SQL Server supports both updation and indexing on views.
The example query written in MS SQL Server is attached.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
