Indexed Views With Outer Joins

Creating a indexed view in SQL Server is damn easy, I just learned it a few days ago, I’ll create a separate post for that. But one restriction I stuck when I was working on one is we can’t use Outer Joins, Union etc in indexed view. If we used SQL Server will punch our face with below error:

Cannot create index on view “Database.View” because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

So what’s the solution?

Simple as the error message suggest convert all your Outer Joins in Inner Joins using NULL checking for appropriate columns.

Okay, so let’s talk some code here…

<pre>/* Create a base table father */
CREATE TABLE Father (
   Father_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	Father_Name varchar(50)
   )
GO 

/* and a son Table to connect with father */
CREATE TABLE Son
  (
  Father_id  smallint, /*Foreign key*/
  Paternity varchar(50)
  )
GO

/* let's create some fathers */
INSERT INTO Father values('Father 1')
INSERT INTO Father values('Father 2')
INSERT INTO Father values('Father 3')

/* and now some sons to give headache to fathers */
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 with inner join */
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, this time with a left join */
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, thrice actually(with right join this time though) */
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

/* 
  So let us create a schemabinded view for full text indexing.
  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 
 
/* let see some families we created */
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

/* Now get some more population in this small world, and test it  */
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                                                       

/* Here we go.... yippie!! */
SELECT * FROM Family
GO</pre>

code courtesy: SQl Server Central

have fun !!!

Advertisements

Say something : I accept all the "Humer&Critic"

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s