Sometime ago I´m in trouble with a massive and large view. This monster was as slow a performing view as I’ve seen and I started to put in an effort to speed up the server and lower the response time.

First I considered creating some indexes on the view, but there a lot of limitations on indexed views. No unions, no sub-queries, no reference to other views and no OUTER JOINS! A legitimately needed left join was present in the view and any attempt to create a index raised a “not allowed construct” error. After hours of trial and error I managed to do it work. It’s not at all an elegant approach, but it works for outer joins. Unions, sub-queries and temporary tables can be emulated by these outer joins, and these can emulate them too.

The Solution

The idea is quite simple. Just emulate a outer join with a inner join! Swamp the outer join with a inner join and put a isnull(table_id,0) at one side of the comparison. The code below shows a full example:

CREATE TABLE Father
(
Father_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Father_Name varchar(50)
)
GO
CREATE TABLE Son
(
Father_id  smallint, /*Foreign key*/
Paternity varchar(50)
)
GO
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’)
GO
/* 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
GO
/* 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
GO
/* Yep, do u need to put the owners names to bind the view to the schema */
CREATE VIEW [dbo].[Family] WITH SCHEMABINDING
AS
/* Yes! You are right this is equal to the select example ; */
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
GO
SELECT * FROM Family
GO
/* Hey!!! It not worked! We are forgetting one important thing to do   */
/* we need a row at the father table to be the “null” or no father row */
SET IDENTITY_INSERT Father ON
INSERT INTO Father (Father_id, Father_name) values(-255,’No father’)
SET IDENTITY_INSERT Father OFF
GO
/* Now create your indexes!!! */
CREATE  UNIQUE  CLUSTERED  INDEX [Pk_Paternity]
ON [dbo].[Family]([paternity])
ON [PRIMARY]
GO
CREATE  INDEX [Pk_father_name]
ON [dbo].[Family]([father_name])
ON [PRIMARY]
GO
INSERT INTO Son values(2,’Child 2B of father 2′)
INSERT INTO Son values(2,’Child 2C of father 2′)
INSERT INTO Son values(null,’Child 0Y of no father’)
INSERT INTO Son values(null,’Child 0Z of no father’)
GO
SELECT * FROM Family
GO

Conclusions

Before your start to put indexes at all views, try use all your other tricks to enhance performance first. Use tables indices, normalization, disk IO, etc. Indexed views are one more tool at hand and this article is just one more tip to help you use them.

One area that always, unfailingly pulls my interest is SQL Server Errors and their solution. I enjoy the challenging task of passing through the maze of error to find a way out with a perfect solution. However, when I received the following error from one of my regular readers, I was a little stumped at first! After some online probing, I figured out that it was actually syntax from MySql and not SQL Server. The reader encountered error when he ran the following query.

ALTER TABLE Table1
DROP PRIMARY KEY
GO

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘PRIMARY’.

As mentioned earlier, this syntax is for MySql, not SQL Server. If you want to drop primary key constraint in SQL Server, run the following query.

ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

Let us now pursue the complete example. First, we will create a table that has primary key. Next, we will drop the primary key successfully using the correct syntax of SQL Server.

CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (
Col1 ASC)
)
GO

/* For SQL Server/Oracle/MS ACCESS */
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

/* For MySql */
ALTER TABLE Table1
DROP PRIMARY KEY
GO

sp_helptext ‘DBObjectName’

[Or]

select object_definition(object_id(’DBObjectName’))

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO