SQL Database Engine Views - Microsoft Docs

SQL Server Views

A view is a virtual table based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add data definitions and other metadata to views. This can help to standardize data, simplify complex queries, and enhance security by limiting access to specific columns or rows.

Key Concepts and Benefits of Views

Data Abstraction: Hide the complexity of underlying tables. Users can interact with a view as if it were a simple table, without needing to know the details of joins or complex filtering.
Security: Restrict access to sensitive data. You can create views that expose only specific columns or rows of a table, preventing unauthorized users from seeing sensitive information.
Consistency: Ensure that complex logic or calculations are applied consistently across the database. Define the logic once in a view, and all users accessing that view will get the same standardized results.
Simplification: Make it easier to write and understand queries. Common join operations or aggregations can be encapsulated within a view, allowing users to write simpler SELECT statements.
Backward Compatibility: If the underlying table structure changes, you can often modify the view to maintain the same column names and structure, minimizing the impact on applications that use the view.

Creating Views

The basic syntax for creating a view is as follows:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
                

Example: Creating a Simple View

Consider a table named Employees with columns EmployeeID, FirstName, LastName, Department, and Salary. We can create a view to show only the names and departments of employees in the 'Sales' department.

CREATE VIEW SalesEmployees AS
SELECT FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';
                

Once created, you can query the view like a regular table:

SELECT *
FROM SalesEmployees;
                

Types of Views

Simple Views: Views that are based on a single table.
Complex Views: Views that involve joins between multiple tables, aggregate functions, or subqueries.
Indexed Views: Views that have an index created on them. Indexed views materialize the view's data, which can significantly improve query performance for complex aggregations.
System Views: Built-in views provided by SQL Server to expose metadata and diagnostic information about the database engine and its objects. Examples include sys.objects and sys.tables.

Modifying and Dropping Views

To modify an existing view, you can use ALTER VIEW or drop and recreate it.

ALTER VIEW SalesEmployees AS
SELECT FirstName, LastName, Department, EmployeeID
FROM Employees
WHERE Department = 'Sales' OR Department = 'Marketing';

-- Or, drop and recreate
-- DROP VIEW SalesEmployees;
-- CREATE VIEW SalesEmployees AS ...
                

To remove a view, use the DROP VIEW statement:

DROP VIEW SalesEmployees;

Commonly Used System Views

SQL Server provides numerous system catalog views that offer valuable information about database objects and the server itself.

Table of Commonly Used System Views

View Name	Description
`sys.objects`	Contains a row for each schema-scoped object in the database.
`sys.tables`	Contains a row for each user-defined table in the database.
`sys.columns`	Contains a row for each column in the database.
`sys.procedures`	Contains a row for each stored procedure in the database.
`sys.views`	Contains a row for each view in the database.
`sys.sql_modules`	Contains a row for each SQL module (e.g., stored procedures, views, triggers).

Example: Listing All Tables and Their Creation Dates

SELECT name, create_date
FROM sys.tables;
                