VIEWS IN SQL SERVER

A View is a method of Encapsulating a Complex query into a simple form, For example, your Client has no idea how to write Complex Queries with multiple joins, Aggregate functions, and Complex SQL Functions, so you can create a View and now your client has to run a SIMPLE select statement against a view and the problem is solved.

A view can be used to Focus on Specific data or a view can be used to Simplify Complex data which can be presented in a simple format and the view can be used to Customize data

VIEWS AS A SECURITY OPTION (ROW-LEVEL SECURITY)

Suppose you have got a request from the “HR” team to share the list of all the employees working in the “HR” Departments. Now your Departments table has data for other Departments as well such as “IT” and “PAYROLL”, so it’s obvious that you cannot share the table name with the “HR” team, instead you can create a view and in that view, YOU can include only department ID “HR” and share the view name with the HR team.

This can provide a way to hide sensitive information from the users, in this case, you are hiding “IT” and “payroll” department information from “HR” guys.

This is also known as row-level security where you want a specific department to see the information which they are intended to see and hide information related to other departments.

IF OBJECT_ID(N'DEPARTMENTS', N'U') IS NOT NULL           
DROP TABLE DEPARTMENTS;
IF OBJECT_ID(N'VW_DEPARTMENTS_HR', N'V') IS NOT NULL           
DROP VIEW VW_DEPARTMENTS_HR;
CREATE TABLE DEPARTMENTS
(EMP_NAME VARCHAR(20),DEPARTMENTS VARCHAR(20),SALARY INT);
INSERT INTO DEPARTMENTS VALUES
('ROB','HR',5000),('RAY','IT',7000),('ROB','PAYROLL',5000);
GO
CREATE VIEW VW_DEPARTMENTS_HR
AS
SELECT * FROM DEPARTMENTS WHERE DEPARTMENTS='HR';
GO
SELECT * FROM VW_DEPARTMENTS_HR;

VIEWS AS A SECURITY OPTION (COLUMN-LEVEL SECURITY)

Suppose you have got a request from the HR team and you need to share the list of all the employees working in the HR department but in the department’s table, you also have the salary column and you don’t want to share the salaries of employees working in the HR department

IF OBJECT_ID(N'DEPARTMENTS', N'U') IS NOT NULL           
DROP TABLE DEPARTMENTS;
IF OBJECT_ID(N'VW_DEPARTMENTS_HR', N'V') IS NOT NULL           
DROP VIEW VW_DEPARTMENTS_HR;
CREATE TABLE DEPARTMENTS
(EMP_NAME VARCHAR(20),DEPARTMENTS VARCHAR(20),SALARY INT);
INSERT INTO DEPARTMENTS VALUES
('ROB','HR',5000),('RAY','IT',7000),('ROB','PAYROLL',5000);
GO
CREATE VIEW VW_DEPARTMENTS_HR
AS
SELECT EMP_NAME,DEPARTMENTS--,SALARY /*Hiding the salary Column/*
FROM DEPARTMENTS WHERE DEPARTMENTS='HR';
GO
SELECT * FROM VW_DEPARTMENTS_HR;

WITH CHECK OPTION

WITH CHECK OPTION allows the view to check that all modifications to data through the view should fall within the scope of the view.

In the below example, we have created a view with the condition in where clause “WHERE Location_ID=1” so now if we try to update the view and try to update the location_Id =2 where location_Id = 1 then without the WITH CHECK OPTION all the Location_ID which were previously Equals to 1 now becomes 2, remember? our view is made from the where condition “Where Location_Id=1” so in this case when all the Location_Ids are updated to 2 the existence of the view vanishes. to stop this kind of behavior we use WITH CHECK OPTION.

With “WITH CHECK OPTION” in place, SQL Server will throw an error as shown in the screenshot below and will not allow us to update the Location_ID from 1 to 2.

“The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint“

IF OBJECT_ID(N'products', N'U') IS NOT NULL           
DROP TABLE products;
IF OBJECT_ID(N'PRODUCTS_WITH_CHECK_OPTION', N'V') IS NOT NULL           
DROP VIEW PRODUCTS_WITH_CHECK_OPTION;
create table products (Location_ID int,Location_Name varchar(20));
insert into products values (1,'USA'),(2,'CHINA'),(3,'INDIA'),(4,'USA');
SELECT * FROM PRODUCTS;
GO
CREATE VIEW PRODUCTS_WITH_CHECK_OPTION AS 
SELECT * FROM PRODUCTS WHERE Location_ID=1 WITH CHECK OPTION;
GO
SELECT * FROM PRODUCTS_WITH_CHECK_OPTION;
UPDATE PRODUCTS_WITH_CHECK_OPTION SET Location_ID=5 WHERE Location_ID=1;
SELECT * FROM PRODUCTS_WITH_CHECK_OPTION;
SELECT * FROM PRODUCTS;

WITH ENCRYPTION

WITH ENCRYPTION allows you to hide the code of the View,

View with “WITH ENCRYPTION ” Clause

IF OBJECT_ID(N'products', N'U') IS NOT NULL           
DROP TABLE products;
IF OBJECT_ID(N'PRODUCTS_WITH_ENCRYPTION', N'V') IS NOT NULL           
DROP VIEW PRODUCTS_WITH_CHECK_OPTION;
create table products (Location_ID int,Location_Name varchar(20));
insert into products values (1,'USA'),(2,'CHINA'),(3,'INDIA'),(4,'USA');
GO
CREATE VIEW PRODUCTS_WITH_ENCRYPTION 
WITH ENCRYPTION 
AS 
SELECT * FROM PRODUCTS WHERE Location_ID=1 ;
GO
SP_HELPTEXT PRODUCTS_WITH_ENCRYPTION

View without “WITH ENCRYPTION ” Clause

IF OBJECT_ID(N'products', N'U') IS NOT NULL           
DROP TABLE products;
IF OBJECT_ID(N'PRODUCTS_WITHOUT_ENCRYPTION', N'V') IS NOT NULL           
DROP VIEW PRODUCTS_WITH_CHECK_OPTION;
create table products (Location_ID int,Location_Name varchar(20));
insert into products values (1,'USA'),(2,'CHINA'),(3,'INDIA'),(4,'USA');
GO
CREATE VIEW PRODUCTS_WITHOUT_ENCRYPTION 
--WITH ENCRYPTION 
AS 
SELECT * FROM PRODUCTS WHERE Location_ID=1 ;
GO
SP_HELPTEXT PRODUCTS_WITHOUT_ENCRYPTION

SCHEMABINDING option

SCHEMABINDING allows your VIEW to not get orphaned in case the base table is Dropped, Suppose you have created a view on a table and someone by mistake or intentionally drops the base table, what will happen to the view?

IF OBJECT_ID(N'PRODUCTS_WITH_SCHEMABINDING', N'V') IS NOT NULL           
DROP VIEW PRODUCTS_WITH_SCHEMABINDING;
IF OBJECT_ID(N'products', N'U') IS NOT NULL           
DROP TABLE products;
create table products (Location_ID int,Location_Name varchar(20));
insert into products values (1,'USA'),(2,'CHINA'),(3,'INDIA'),(4,'USA');
GO
CREATE VIEW PRODUCTS_WITH_SCHEMABINDING 
WITH SCHEMABINDING 
AS 
SELECT Location_ID,Location_Name FROM DBO.PRODUCTS WHERE Location_ID=1 ;

Now if we try to drop the base table “PRODUCTS” then we will get the below error.

DROP TABLE products;

Materialized Views or Indexed Views

A Materialized or Indexed View is similar to a normal view, However, the Materialized view is stored in the database similar to an index on a table.

Please note that the view is still a virtual table, but the index on the view is physically stored in the database.

The first index on a Materialized view must be a clustered Index, after that, you can add a non-clustered index as well.

The view on which you want to create an index must be DETERMINISTIC. and PRECISE

Any Index created on a view should be created using WITH SCHEMABINDING Option

If you are using a GROUP BY Clause in a view then you can use only the columns which are used for Unique Clustered Index.

SPACE USED IN A NORMAL VIEW

In the below example, we are creating a normal view and will see how much space a normal view uses, a normal view is still a virtual table and will not take up any space in a database.

IF OBJECT_ID(N'Normal_View_Test', N'V') IS NOT NULL           
DROP VIEW Normal_View_Test 
GO
IF OBJECT_ID(N'DBO.EMPLOYEES_Normal_View', N'U') IS NOT NULL           
DROP TABLE DBO.EMPLOYEES_Normal_View 
GO

CREATE TABLE DBO.EMPLOYEES_Normal_View
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO DBO.EMPLOYEES_Normal_View VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from DBO.EMPLOYEES_Normal_View 
GO
--NORMAL VIEW
CREATE VIEW Normal_view_Test 
WITH SCHEMABINDING
AS
SELECT ID,F_NAME,L_NAME,SALARY,dept FROM DBO.EMPLOYEES_Normal_View
GO
SELECT * FROM Normal_view_Test
GO
sp_spaceused '[Normal_view_Test]' 
GO

SPACE USED IN A MATERIALIZED OR INDEXED VIEW

In the below example, we are creating a Deterministic Materialized view and will see how much space a Materialized view uses, a Materialized view is still a virtual table and but when a Unique Clustered Index is created on it, That index will be stored in the database like any other index on a table.

IF OBJECT_ID(N'Materialized_Test', N'V') IS NOT NULL           
DROP VIEW Materialized_Test
GO
IF OBJECT_ID(N'DBO.EMPLOYEES_Materialized_Test', N'U') IS NOT NULL           
DROP TABLE DBO.EMPLOYEES_Materialized_Test
GO

CREATE TABLE DBO.EMPLOYEES_Materialized_Test
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO DBO.EMPLOYEES_Materialized_Test VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from DBO.EMPLOYEES_Materialized_Test
GO

--Materialized VIEW WITH DETERMINISTIC FUNCTION
CREATE VIEW Materialized_Test 
WITH SCHEMABINDING
AS
SELECT ID,F_NAME,L_NAME,SALARY,dept FROM DBO.EMPLOYEES_Materialized_Test
GO
SELECT * FROM Materialized_Test;
DROP INDEX IF EXISTS ix_index on Materialized_Test
CREATE UNIQUE CLUSTERED INDEX ix_index ON Materialized_Test(SALARY)
GO
sp_spaceused '[Materialized_Test]' 
GO

CAN WE USE A DETERMINISTIC FUNCTION IN A MATERIALIZED OR INDEXED VIEW

A Deterministic function will always return the same result on a given input value, In the below query we are using “SALARY+100” to get the incremented salary of an employee, this computed column is the deterministic column as adding something to the salary will always yield the same value. So in this case we will be able to create a view on a table with a Deterministic computed column without any issues.

IF OBJECT_ID(N'Deterministic_Materialized_Test', N'V') IS NOT NULL           
DROP VIEW Deterministic_Materialized_Test
GO
IF OBJECT_ID(N'DBO.EMPLOYEES_Deterministic_Materialized_Test', N'U') IS NOT NULL           
DROP TABLE DBO.EMPLOYEES_Deterministic_Materialized_Test
GO

CREATE TABLE DBO.EMPLOYEES_Deterministic_Materialized_Test
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO DBO.EMPLOYEES_Deterministic_Materialized_Test VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from DBO.EMPLOYEES_Deterministic_Materialized_Test
GO

--Materialized VIEW WITH DETERMINISTIC FUNCTION
CREATE VIEW Deterministic_Materialized_Test 
WITH SCHEMABINDING
AS
SELECT ID,F_NAME,L_NAME,SALARY,dept, salary+100 incremented_salary FROM DBO.EMPLOYEES_Deterministic_Materialized_Test
GO
SELECT * FROM Deterministic_Materialized_Test;
DROP INDEX IF EXISTS ix_index on Deterministic_Materialized_Test
CREATE UNIQUE CLUSTERED INDEX ix_index ON Deterministic_Materialized_Test(SALARY)
GO
sp_spaceused '[Deterministic_Materialized_Test]' 
GO

CAN WE USE A NON-DETERMINISTIC FUNCTION IN A MATERIALIZED OR INDEXED VIEW

IF OBJECT_ID(N'NONDETERMINISTIC_Materialized_Test', N'V') IS NOT NULL           
DROP VIEW NONDETERMINISTIC_Materialized_Test
GO
IF OBJECT_ID(N'DBO.EMPLOYEES_NONDeterministic_Materialized_Test', N'U') IS NOT NULL           
DROP TABLE DBO.EMPLOYEES_NONDeterministic_Materialized_Test
GO

CREATE TABLE DBO.EMPLOYEES_NONDeterministic_Materialized_Test
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO DBO.EMPLOYEES_NONDeterministic_Materialized_Test VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from DBO.EMPLOYEES_NONDeterministic_Materialized_Test
GO

--Materialized VIEW WITH NON-PRECISE FUNCTION
CREATE VIEW NONDETERMINISTIC_Materialized_Test 
WITH SCHEMABINDING
AS
SELECT ID,F_NAME,L_NAME,SALARY,dept,GETDATE() Tdy_date FROM DBO.EMPLOYEES_NONDeterministic_Materialized_Test
GO
SELECT * FROM NONDETERMINISTIC_Materialized_Test;
DROP INDEX IF EXISTS ix_index on NONDETERMINISTIC_Materialized_Test
CREATE UNIQUE CLUSTERED INDEX ix_index ON NONDETERMINISTIC_Materialized_Test(SALARY)
GO
sp_spaceused '[NONDETERMINISTIC_Materialized_Test]'

CAN WE USE A NONPRECISE FUNCTION IN A MATERIALIZED OR INDEXED VIEW

IF OBJECT_ID(N'NONPRECISE_Materialized_Test', N'V') IS NOT NULL           
DROP VIEW NONPRECISE_Materialized_Test
GO
IF OBJECT_ID(N'DBO.EMPLOYEES_NONPRECISE_Materialized_Test', N'U') IS NOT NULL           
DROP TABLE DBO.EMPLOYEES_NONPRECISE_Materialized_Test
GO

CREATE TABLE DBO.EMPLOYEES_NONPRECISE_Materialized_Test
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20));
INSERT INTO DBO.EMPLOYEES_NONPRECISE_Materialized_Test VALUES
(1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR');

select * from DBO.EMPLOYEES_NONPRECISE_Materialized_Test
GO

--Materialized VIEW WITH NON-PRECISE FUNCTION
CREATE VIEW NONPRECISE_Materialized_Test 
WITH SCHEMABINDING
AS
SELECT ID,F_NAME,L_NAME,SALARY*CAST(.009 AS FLOAT) SALARY,dept FROM DBO.EMPLOYEES_NONPRECISE_Materialized_Test
GO
SELECT * FROM NONPRECISE_Materialized_Test;
DROP INDEX IF EXISTS ix_index on NONPRECISE_Materialized_Test
CREATE UNIQUE CLUSTERED INDEX ix_index ON NONPRECISE_Materialized_Test(SALARY)
GO
sp_spaceused '[NONPRECISE_Materialized_Test]'

Partitioned View

A Materialized or Indexed

IF OBJECT_ID(N'ORDER_MONTH', N'V') IS NOT NULL DROP VIEW ORDER_MONTH;
IF OBJECT_ID(N'ORDERS', N'U') IS NOT NULL DROP TABLE ORDERS;
IF OBJECT_ID(N'ORDERS_JAN', N'U') IS NOT NULL DROP TABLE ORDERS_JAN;
IF OBJECT_ID(N'ORDERS_FEB', N'U') IS NOT NULL DROP TABLE ORDERS_FEB;
IF OBJECT_ID(N'ORDERS_MAR', N'U') IS NOT NULL DROP TABLE ORDERS_MAR;

CREATE TABLE ORDERS(order_id INT,order_name VARCHAR(30),order_date VARCHAR(30),order_month VARCHAR(30));

CREATE TABLE ORDERS_JAN(order_id INT,order_name VARCHAR(30),order_date VARCHAR(30),order_month SMALLINT NOT NULL CONSTRAINT CK_OM_M1 CHECK (order_month = 1) CONSTRAINT PK_MONTH1 PRIMARY KEY (order_id, order_month));
CREATE TABLE ORDERS_FEB(order_id INT,order_name VARCHAR(30),order_date VARCHAR(30),order_month SMALLINT NOT NULL CONSTRAINT CK_OM_M2 CHECK (order_month = 2) CONSTRAINT PK_MONTH2 PRIMARY KEY (order_id, order_month));
CREATE TABLE ORDERS_MAR(order_id INT,order_name VARCHAR(30),order_date VARCHAR(30),order_month SMALLINT NOT NULL CONSTRAINT CK_OM_M3 CHECK (order_month = 3) CONSTRAINT PK_MONTH3 PRIMARY KEY (order_id, order_month));

SELECT * FROM ORDERS;
SELECT * FROM ORDERS_JAN;
SELECT * FROM ORDERS_FEB;
SELECT * FROM ORDERS_MAR;

CREATE VIEW ORDER_MONTH
WITH SCHEMABINDING
AS
SELECT order_id,order_name,order_date,order_month FROM DBO.ORDERS_JAN UNION ALL 
SELECT order_id,order_name,order_date,order_month FROM DBO.ORDERS_FEB UNION ALL 
SELECT order_id,order_name,order_date,order_month FROM DBO.ORDERS_MAR;
GO

INSERT INTO DBO.ORDER_MONTH VALUES(111,'MUK',GETDATE(),1)
INSERT INTO DBO.ORDER_MONTH VALUES(222,'RAJ',GETDATE(),2)
INSERT INTO DBO.ORDER_MONTH VALUES(333,'JAI',GETDATE(),3)
INSERT INTO DBO.ORDER_MONTH VALUES(444,'SAI',GETDATE(),1)

SELECT * FROM ORDERS;
SELECT * FROM ORDERS_JAN;
SELECT * FROM ORDERS_FEB;
SELECT * FROM ORDERS_MAR;

Note

Triggers can be created in Views

VIEWS IN SQL SERVER