SQL Server Views
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 viewExample
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.Still have some questions? Let's discuss.
CONTACT US