Type of views in SQL Server

A view is a database object created using a SELECT statement.  We can say that a View in SQL Server is a virtual table that stores a predefined SELECT query. It does not store data physically; instead, it dynamically returns data from underlying tables each time it is queried.

Based on the nature we have classified view in the below category

Ø  Simple view

Ø  Complex view

Ø  Index view (materialized view)

Ø  Partition view

Ø  Schema bound view

Ø  Updateable view

Ø  View with check option

Ø  Encrypted view

Ø  Read only view

Ø  System view

Ø  Inline table valued view (Logical concept)

Simple view

A Simple View is created using a SELECT statement on one or more tables, without complex constructs. See the example

CREATE VIEW vw_Product_Simple_View_demo AS SELECT productkey,        productalternatekey,        englishproductname,        englishdescription FROM   dimproduct

View created. See the data.

This kind of view is simple view. It does not have aggregation, no GROUP BY, no DISTINCT. We can update the base table using this view if we have update permission on this view.

Complex view

A complex view is a logical abstraction that combines multiple tables using joins, aggregates, expressions, and filters. SQL Server does not store its result set; instead, it expands the view definition into the calling query during compilation, which directly impacts optimization, cardinality estimation, and execution plans.

See the example

USE adventureworksdw2019; go CREATE VIEW dbo.vw_productcategorysalessummary AS   SELECT c.englishproductcategoryname AS ProductCategory,          d.calendaryear               AS SalesYear,          Count_big(*)                 AS OrderCount,          Sum(f.salesamount)           AS TotalSalesAmount,          Avg(f.salesamount)           AS AvgSalesAmount   FROM   factinternetsales f          JOIN dimproduct p            ON f.productkey = p.productkey          JOIN dimproductsubcategory sc            ON p.productsubcategorykey = sc.productsubcategorykey          JOIN dimproductcategory c            ON sc.productcategorykey = c.productcategorykey          JOIN dimdate d            ON f.orderdatekey = d.datekey   WHERE  f.salesamount > 0   GROUP  BY c.englishproductcategoryname,             d.calendaryear; go

See the data

select * from vw_ProductCategorySalesSummary

 

Index view (materialized view)

An indexed view is a view whose result set is physically stored on disk using a clustered index. Unlike normal views, SQL Server maintains this data synchronously during base table DML operations, making indexed views suitable for repetitive, aggregation-heavy read workloads—but expensive for writes.

See below example

USE adventureworksdw2019;   go   CREATE VIEW dbo.vw_internetsales_bycategory_indexed WITH schemabinding AS   SELECT c.englishproductcategoryname AS ProductCategory,          d.calendaryear               AS SalesYear,          Count_big(*)                 AS OrderCount,          Sum(f.salesamount)           AS TotalSalesAmount   FROM   dbo.factinternetsales AS f          JOIN dbo.dimproduct AS p            ON f.productkey = p.productkey          JOIN dbo.dimproductsubcategory AS sc            ON p.productsubcategorykey = sc.productsubcategorykey          JOIN dbo.dimproductcategory AS c            ON sc.productcategorykey = c.productcategorykey          JOIN dbo.dimdate AS d            ON f.orderdatekey = d.datekey   GROUP  BY c.englishproductcategoryname,             d.calendaryear;   go

View is created now creating index on this view

CREATE UNIQUE CLUSTERED INDEX IX_vw_InternetSales_ByCategory ON dbo.vw_InternetSales_ByCategory_Indexed (     ProductCategory,     SalesYear ); GO

Index created. Now this view is known as indexed view or martialized view.

See the data

Select * from dbo.vw_InternetSales_ByCategory_Indexed;

We can see that index is created

With the help of below query we can see the index details

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.vw_InternetSales_ByCategory_Indexed');

 

Partition view

A partitioned view is a UNION ALL view that horizontally partitions data across multiple tables using mutually exclusive CHECK constraints, allowing SQL Server to perform partition elimination at query compile time. It is a logical sharding mechanism, not a physical partitioning feature.

Let’s see the demo.

First, we will create year wise tables

USE adventureworksdw2019;  go  SELECT * INTO   dbo.factinternetsales_2012 FROM   dbo.factinternetsales f        JOIN dbo.dimdate d          ON f.orderdatekey = d.datekey WHERE  d.calendaryear = 2012;  go  SELECT * INTO   dbo.factinternetsales_2013 FROM   dbo.factinternetsales f        JOIN dbo.dimdate d          ON f.orderdatekey = d.datekey WHERE  d.calendaryear = 2013;  go  SELECT * INTO   dbo.factinternetsales_2014 FROM   dbo.factinternetsales f        JOIN dbo.dimdate d          ON f.orderdatekey = d.datekey WHERE  d.calendaryear = 2014;  go

Tables are created now we are adding check constraints on theses tables.

ALTER TABLE dbo.factinternetsales_2012   ADD CONSTRAINT ck_factinternetsales_2012 CHECK (orderdatekey BETWEEN 20120101   AND 20121231); ALTER TABLE dbo.factinternetsales_2013   ADD CONSTRAINT ck_factinternetsales_2013 CHECK (orderdatekey BETWEEN 20130101   AND 20131231); ALTER TABLE dbo.factinternetsales_2014   ADD CONSTRAINT ck_factinternetsales_2014 CHECK (orderdatekey BETWEEN 20140101   AND 20141231); go

Now creating view

CREATE VIEW dbo.vw_factinternetsales_partitioned AS   SELECT *   FROM   dbo.factinternetsales_2012   UNION ALL   SELECT *   FROM   dbo.factinternetsales_2013   UNION ALL   SELECT *   FROM   dbo.factinternetsales_2014;

See the data

SELECT * FROM dbo.vw_FactInternetSales_Partitioned WHERE OrderDateKey BETWEEN 20130101 AND 20131231;

 

Schema bound view

A schema-bound view is a view that is tightly bound to the schema of its underlying objects. SQL Server prevents any schema changes to the base tables that would invalidate the view definition, ensure structural stability and make the view eligible for indexed views.

Let’s see the demo

First, we are creating a normal view. This view is not schema bounded

CREATE OR ALTER VIEW dbo.vw_InternetSales_Normal AS SELECT     d.CalendarYear,     SUM(f.SalesAmount) AS TotalSales,               SUM(f.DemoColumn1) as DemoTotal FROM dbo.FactInternetSales f JOIN dbo.DimDate d     ON f.OrderDateKey = d.DateKey GROUP BY     d.CalendarYear; GO

See the data

Now we are trying to drop a column which is used in this view.

alter table FactInternetSales  drop column DemoColumn1

Column dropped successfully.

It allows us to do schema changes on this base table. But when we run the view it will throws an error.

To protect the drop, we are creating schema bounded view. Let’s see the below example.

 

USE AdventureWorksDW2019; GO   CREATE OR ALTER VIEW dbo.vw_InternetSales_SchemaBound WITH SCHEMABINDING AS SELECT     d.CalendarYear,     SUM(f.SalesAmount) AS TotalSales,               SUM(f.DemoColumn1) as DemoTotal FROM dbo.FactInternetSales f JOIN dbo.DimDate d     ON f.OrderDateKey = d.DateKey GROUP BY     d.CalendarYear; GO

See the data

Now we are altering the base table columns which are used in this view.

alter table FactInternetSales  drop column DemoColumn1

Let’s try to drop or alter another column from the based table which are not used in this view.

We can able to do the changes which are not used in this view.

Updateable view

An updatable view is a view through which INSERT, UPDATE, or DELETE operations are allowed, provided SQL Server can deterministically map each modified column to a single base table row without ambiguity.

A view IS updatable if it references one base table and having no aggregates, no GROUP BY, no DISTINCT, no UNION, no computed columns, no Top Clause no multiple base tables no windows function no union or union all. Only sample view is updatable.

Let’s see the demo

CREATE OR ALTER VIEW dbo.vw_DimEmployee_Updatable AS SELECT     EmployeeKey,     FirstName,     LastName,     Title,     EmailAddress FROM Employee_Updatable_demo; GO

Let’s insert the record into the base table using this view.

INSERT INTO dbo.vw_DimEmployee_Updatable (     EmployeeKey,     FirstName,     LastName,     Title,     EmailAddress ) VALUES (9999,'BageshKumar', 'Singh', 'Analyst', 'Bksingh@gamil.com');   select *  FROM dbo.Employee_Updatable_demo where EmployeeKey=9999

Let’s update this record.

Let’s delete this  record.

Truncate is not working on view. If we want to delete all records we need to use delete command only. We cannot update a view which has completed column.

View with check option

WITH CHECK OPTION ensures that any INSERT or UPDATE performed through a view does not result in rows that fall outside the view’s defining WHERE clause. SQL Server validates the view predicate after the DML operation and rejects changes that would make the row invisible to the view.

WITH CHECK OPTION enforces the view’s WHERE clause on DML, not on SELECT.

Let’s see the demo

Creating a table and inserting few records

CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY,     Name VARCHAR(100),     DepartmentID INT,     Salary DECIMAL(10, 2) ); GO   -- Insert some initial data INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (1, 'Bagesh', 10, 60000.00), (2, 'Rajesh', 20, 75000.00), (3, 'Umesh', 10, 50000.00), (4, 'Suresh', 10, 50000.00), (5, 'Mahesh', 20, 50000.00); GO

Now creating a view

CREATE VIEW View_Dept10_Employees AS SELECT EmployeeID, Name, DepartmentID, Salary FROM Employees WHERE DepartmentID = 10 WITH CHECK OPTION; GO

View created successfully.

Using this view we can only update, insert the records which has departmentID 10 only. Apart from that it will throw an error and view does not allow us to perform Insert and update operation on the base table. It is not

Let’s see

 See the data

Let’s insert a record in the base table using view

INSERT INTO View_Dept10_Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (6, 'Amit', 10, 55000.00); GO

Record inserted successfully.

Let’s try to insert other records which department is not 10.

INSERT INTO View_Dept10_Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (7, 'Mohit', 30, 55000.00);

Throwing an error. We cannot insert the records in the base table. We can’t update the records as well

update View_Dept10_Employees set DepartmentID=20 where EmployeeID=1

Encrypted view

An encrypted view is a view created with WITH ENCRYPTION that obfuscates its definition in system catalog views, preventing users from viewing the underlying SQL text. It does not encrypt data, improve security, or prevent data access. WITH ENCRYPTION hides the view definition — NOT the data.

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY,     Name VARCHAR(100),     DepartmentID INT,     Salary DECIMAL(10, 2) ); GO  -- Insert some initial data INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary) VALUES (1, 'Bagesh', 10, 60000.00), (2, 'Rajesh', 20, 75000.00), (3, 'Umesh', 10, 50000.00), (4, 'Suresh', 10, 50000.00), (5, 'Mahesh', 20, 50000.00); GO

Creating normal view

CREATE VIEW vw_Employees AS SELECT EmployeeID, Name, DepartmentID, Salary FROM Employees; GO

View created. We can see the definition of this view.

EXEC sp_helptext 'dbo.vw_Employees';

We are able to see the definition of the view.

Now creating encrypted view.

CREATE VIEW vw_Employees_enc WITH ENCRYPTION AS SELECT EmployeeID, Name, DepartmentID, Salary FROM Employees; GO

View created successfully. Now see the definition of this view.

EXEC sp_helptext 'dbo.vw_Employees_enc';

 

But we can see the data from both view

Read only view

A read-only view is a view that allows only SELECT operations and prevents INSERT, UPDATE, and DELETE operations, typically enforced using permissions, INSTEAD OF triggers, or non-updatable view definitions. SQL Server does not provide a native READ ONLY view keyword. There is NO CREATE VIEW … READ ONLY syntax in SQL Server. Read-only behavior is enforced logically, not syntactically.

 

System view

System views in SQL Server are built-in metadata views that expose information about database objects, configuration, security, and runtime state. They provide a supported, read-only interface to SQL Server’s internal catalog and engine state. System views are NOT user data views.
They expose metadata and engine information, not business data.

Inline table valued view (Logical concept)

An “inline table-valued view” is a logical concept describing how SQL Server treats a simple view the same way it treats an inline table-valued function—by expanding it directly into the calling query during optimization, without materialization, statistics, or intermediate storage. There is no object type called “inline table-valued view” in SQL Server. It is an optimizer behavior, not a DDL feature.