In this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script
If(OBJECT_ID('PerfTest1') is Not null)
Drop Table PerfTest1
Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30))
Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')
Go 300
If(OBJECT_ID('PerfTest2') is Not null)
Drop Table PerfTest2
Create Table PerfTest2(Id int Identity(1,1),Code Int,CodeName Char(40))
Insert Into PerfTest2 Values(120,'LIVE'),(99,'ACTION'),(100,'SLOW'),(200,'PROCESS'),(200,'OUTDOOR')
Go 300
Create Following View
Create View Vw_PerfUnion
As
Select CodeName From PerfTest1
Union
Select CodeName From PerfTest2
Go
Run Following Query and Check the Execution Plan
Select * from Vw_PerfUnion Where CodeName='Live'
Its obvious that table scan will take since no index has been create on tables. Now lets create index on these tables.
Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go Create NonClustered Index IX_PerfTest2 On PerfTest2(CodeName)
Go
Now Run Query Again and Check Execution Plan
Select * from Vw_PerfUnion Where CodeName='Live'
As you can see in above screen shot even after creating index on table PerfTest1 Table Scan is taking Place. This is due to the Datatype mismatch between Two Query in the view. Check the datatype of PerfTest1(CodeName) and PerfTest1(CodeName) Fields they are Char(30) and Char(40) respectively. Now Lets alter the Datatype of PerfTest1 Table. To alter field we need to drop index we have created on that field.
Drop Index PerfTest1.IX_PerfTest1
Go
Alter Table PerfTest1 Alter Column CodeName Varchar(40)
Go
Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go
Now run the Query and Check Execution Plan
