Today I saw one question in technet forum where one of member have asked this question
Q. If i have index on same column on table and view of same table which one will be used?
To answer this i have run below scenario with (10. 100 and 1000 rows) and reached on conclusion that.
If both table and view have seperate clustered index on same column then sql server will always refer clustered index on table.
Below is scenario:
/*One should always run test in tempdb so that wit sql restart all created objects should dropped*/
/* Create Table */
CREATE TABLE Employee(empid INT, name VARCHAR(100), deptid INT)
INSERT INTO Employee(empid,name,deptid)
SELECT TOP 10 ROW_NUMBER()OVER (ORDER BY a.name),
a.name ,ROW_NUMBER()OVER (ORDER BY a.name DESC)
FROM sys.all_objects a CROSS JOIN sys.all_objects b
/* Create Index on Table */
CREATE UNIQUE CLUSTERED INDEX [CI_Employee] ON Employee
( EMPID ASC )
/* Create View*/
CREATE VIEW Employee_View
/*Create Index on View*/
CREATE UNIQUE CLUSTERED INDEX [CI_Employee_View] ON Employee_View
/*Enable execution plan using CTRL + M or in query option in menu bar*/
So execution plan clearly shows that sql server always use index on table