How To Create a Full Text Indexed View or Schema Binded View

How To Create a Full Text Indexed View or Schema Binded View

To create a schema binded view or full text index on a view, we simply have to write a select statement as we do while creating any normal view fetching data from one or more tables. The only thing different we do here is creating a this view with schema binding.

Once we created a schema-bound view, we can add clustered or non-clustered indexes to this view.

Check below self explanatory example below that creates a full text index to get family details from Father and Son tables.

	/* 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 add some sons "Son" table */
	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
	/* Testing tables 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
	/* Testing tables again (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 index.
	*/
	CREATE VIEW [dbo].[Family] WITH SCHEMABINDING
	AS
	/* Hey! It looks like a select query. cool ;) */
	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 didn't work! We are forgetting one important thing to do */
	/* we need a row at the father table to be the "null" or a 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.... */
	SELECT * FROM Family
	GO

view raw indexed_view_with_outer_join.sql hosted with ❤ by GitHub