Published on

September 18, 2007

Improving Performance with Indexed Views in SQL Server

As a SQL Server developer, you may encounter situations where you need to optimize the performance of a slow-performing view. In such cases, creating indexes on the view can be a potential solution. However, indexed views come with certain limitations, such as no support for unions, sub-queries, reference to other views, and outer joins.

In this article, we will explore a workaround to overcome the limitation of outer joins in indexed views. By emulating an outer join with an inner join, we can achieve the desired results. Let’s dive into the solution with an example:

CREATE TABLE Father (
   Father_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   Father_Name varchar(50)
)

CREATE TABLE Son (
   Father_id  smallint, /*Foreign key*/
   Paternity varchar(50)
)

INSERT INTO Father values('Father 1')
INSERT INTO Father values('Father 2')
INSERT INTO Father values('Father 3')
INSERT INTO Son values(1,'Child 1A of father 1')
INSERT INTO Son values(1,'Child 1B of father 1')  
INSERT INTO Son values(2,'Child 2A of father 2')   
INSERT INTO Son values(null,'Child 0X of no father')   

/* Test your tables */
SELECT f.father_id, f.father_name, s.father_id, s.paternity
FROM father f
INNER JOIN son s 
ON s.father_id=f.father_id

/* Test your tables twice*/
SELECT f.father_id, f.father_name, s.father_id, s.paternity
FROM father f
LEFT JOIN son s 
ON s.father_id=f.father_id

/* Test your tables twice*/
SELECT f.father_id, f.father_name, s.father_id, s.paternity
FROM father f
RIGHT JOIN son s 
ON s.father_id=f.father_id

/* Create a view with schema binding */
CREATE VIEW [dbo].[Family] WITH SCHEMABINDING 
AS
SELECT f.father_id, f.father_name, s.father_id as son_id, s.paternity
FROM [dbo].[father] f
INNER JOIN [dbo].[son] s 
ON ISNULL(s.father_id, -255)=f.father_id

/* Insert a row for "null" or no father */
SET IDENTITY_INSERT Father ON
INSERT INTO Father (Father_id, Father_name) values(-255,'No father')
SET IDENTITY_INSERT Father OFF

/* Create indexes on the view */
CREATE UNIQUE CLUSTERED INDEX [Pk_Paternity] ON [dbo].[Family]([paternity]) ON [PRIMARY]
CREATE INDEX [Pk_father_name] ON [dbo].[Family]([father_name]) ON [PRIMARY]

/* Test the indexed view */
SELECT * FROM Family

In the above example, we have a “Father” table and a “Son” table with a foreign key relationship. We initially encounter the limitation of outer joins in indexed views. To overcome this, we emulate the outer join with an inner join by using the ISNULL function to compare the “Father_id” column with a default value of -255.

After creating the view, we insert a row with a “Father_id” of -255 to represent the “null” or no father scenario. Finally, we create indexes on the view to further enhance performance.

It’s important to note that before resorting to indexed views, it’s recommended to explore other performance optimization techniques such as using table indices, normalization, and disk IO. Indexed views should be considered as an additional tool in your optimization toolkit.

By leveraging the workaround discussed in this article, you can overcome the limitation of outer joins in indexed views and improve the performance of your SQL Server queries.

Stay tuned for more SQL Server tips and tricks!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.