What is indexed view and
why do we need it
A view is a virtual table.
A normal view does not
store the data on the disk. Data from the view is obtained only when the view
is executed i.e. just the definition of the view is stored in the database but
not the data.
An Indexed view is one
where index been created on the view. Indexed view store the data on the disk.
Refer the below example:
CREATE TABLE dbo.employees
(
id int NOT NULL,
name
varchar(255) NOT NULL,
CONSTRAINT PK_employees PRIMARY
KEY (id),
CONSTRAINT UQ_employees UNIQUE
(name)
)
CREATE TABLE dbo.roles
(
id int NOT NULL,
name
varchar(255) NOT NULL,
CONSTRAINT PK_roles PRIMARY
KEY (id),
CONSTRAINT UQ_roles UNIQUE (name)
)
CREATE TABLE dbo.lnk_employee_roles
(
employee_id
int NOT NULL,
role_id
int NOT NULL,
CONSTRAINT PK_lnk_employee_roles PRIMARY KEY (employee_id, role_id),
CONSTRAINT FK_lnk_employee_roles__employees FOREIGN KEY (employee_id) REFERENCES dbo.employees (id),
CONSTRAINT FK_lnk_employee_roles__roles FOREIGN KEY (role_id) REFERENCES dbo.roles (id)
)
INSERT INTO dbo.employees (id, name)
VALUES
(1, 'Employee 1'),
(2, 'Employee 2'),
(3, 'Employee 3')
INSERT INTO dbo.roles (id, name)
VALUES
(1, 'Role 1'),
(2, 'Role 2'),
(3, 'Role 3')
INSERT INTO lnk_employee_roles (employee_id, role_id)
VALUES
(1, 1),
(1, 2),
(1, 3),
(3, 2),
(3, 3)
GO
/*Normal View*/
CREATE VIEW dbo.EmployeeRoles_WOSB
AS
SELECT
e.id as employee_id,
e.name as employee_name,
r.id as role_id,
r.name as role_name
FROM
dbo.employees
e
INNER JOIN dbo.lnk_employee_roles er ON
e.id = er.employee_id
INNER JOIN dbo.roles r ON r.id = er.role_id
GO
/* Indexed
View*/
CREATE VIEW dbo.EmployeeRoles_WSB
WITH SCHEMABINDING
AS
SELECT
e.id as employee_id,
e.name as employee_name,
r.id as role_id,
r.name as role_name
FROM
dbo.employees
e
INNER JOIN dbo.lnk_employee_roles er ON
e.id = er.employee_id
INNER JOIN dbo.roles r ON r.id = er.role_id
GO
CREATE UNIQUE CLUSTERED INDEX idx_EmployeeRoles
ON dbo.EmployeeRoles_WSB (employee_id, role_id)
GO
sp_spaceused
EmployeeRoles_WOSB --NULL
GO
sp_spaceused EmployeeRoles_WSB –5
So we can see normal view
returns row count as NULL whereas indexed
view returns row count as 5.
- Suppose consider a normal view has some aggregation/computation/joins then, every time the view is executed aggregation/computation/joins need to happen. This degrades the performance. So if index is created on a view aggregation/computation/joins gets stored physically. When any base table gets updated the view also gets updated.
- View with index has pre-computed data.
- Indexed views work best when the underlying data is not frequently updated.
- Another cost of indexed views is that the data is actually stored. By applying the clustered index we are creating a copy of the data. So if we have several indexed views on a single table, we will have several copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes with it.
- Index can only be created on the schema bound view. On normal view we can’t create the view.
- Without Creating UNIQUE CLUSTERED INDEX we cannot create NON CLUSTERED INDEX on a view.
CREATE TABLE dbo.emps
(
ID INT,
name VARCHAR(50),
dept VARCHAR(50),
company VARCHAR(50)
)
GO
CREATE VIEW dbo.Vemps
WITH SCHEMABINDING
AS
SELECT name,dept,company FROM dbo.emps
GO
CREATE nonCLUSTERED INDEX
id_View_Vemps_non_clust
ON dbo.vemps(company)
Msg 1940, Level 16,
State 1, Line 1
Cannot create index
on view 'dbo.vemps'. It does not have a unique clustered index.
CREATE unique CLUSTERED INDEX id_View_Vemps
ON dbo.vemps(name,dept)
GO
Command(s) completed
successfully.
CREATE nonCLUSTERED INDEX
id_View_Vemps_non_clust
ON dbo.vemps(company)
Command(s) completed
successfully.
No comments:
Post a Comment