SQL Server Views

Level : Advanced

Mentor: Shailendra Chauhan

Duration : 00:02:00

User-defined Views

User-defined views in SQL Server allow you to create virtual tables based on existing tables or other views. They simplify data access by presenting a customized and abstracted view of the data.

Example

CREATE VIEW EmployeeNames AS
SELECT FirstName, LastName FROM Employees;

Indexed Views

Indexed views, also known as materialized views, store the result of a query physically in the database. They improve query performance by precomputing and indexing data.

Example

CREATE INDEX IX_SalesSummary_Product ON SalesSummaryView (ProductID);

Partitioned Views

Partitioned views allow you to divide a large table into smaller, more manageable partitions. Each partitioned view represents a subset of the data, improving query performance and management.

System Views

System views are predefined views in SQL Server that provide metadata about the database, its objects, and configuration settings. Examples include sys.tables and sys.columns.

Create SQL VIEW in SQL Server

To create a view in SQL Server.

Example

CREATE VIEW MyView AS
SELECT * FROM MyTable WHERE Column1 = 'Value';

Retrieve Data From View in SQL Server

To retrieve data from a view.

Example

SELECT * FROM MyView;

Dropping a View in SQL Server

To drop a view

Example

DROP VIEW MyView;

Renaming the View in SQL Server

To rename a view.

Example

sp_rename 'OldViewName', 'NewViewName';

Getting Information about a View

To get information about a view.

Example

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'MyView';

Alter View in SQL Server

To modify a view.

Example

ALTER VIEW MyView AS
SELECT * FROM UpdatedTable;

Refreshing a View in SQL Server

Views are automatically refreshed when underlying data changes. No explicit refresh is needed.

SchemaBinding a VIEW

To bind a view to the schema of underlying objects.

Example

CREATE VIEW MySchema.MyView WITH SCHEMABINDING AS
SELECT * FROM MyTable;

Encrypt a View in SQL Server

Views do not have direct encryption; you can encrypt underlying tables and data.

Check Option

WITH CHECK OPTION ensures that updates through a view follow the view's filter criteria.

DML Query In View

Views can be used for Data Manipulation Language (DML) operations like INSERT, UPDATE, and DELETE.

System Define Views

System-defined views like sys.objects provide information about database objects.

Information Schema

The INFORMATION_SCHEMA provides standard views to retrieve metadata about the database, including views.

Catalog View

Catalog views like sys.objects contain information about database objects and their properties.

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.