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:
USE TEMPDB
/*One should always run test in tempdb so that wit sql restart all created objects should dropped*/
GO
/* Create Table */
CREATE TABLE Employee(empid INT, name VARCHAR(100), deptid INT)
GO
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
GO
/* Create Index on Table */
CREATE UNIQUE CLUSTERED INDEX [CI_Employee] ON Employee
( EMPID ASC )
GO
/* Create View*/
CREATE VIEW Employee_View
WITH SCHEMABINDING
AS
SELECT empid,name,deptid
FROM dbo.Employee
GO
/*Create Index on View*/
CREATE UNIQUE CLUSTERED INDEX [CI_Employee_View] ON Employee_View
(EMPID ASC)
GO
/*Enable execution plan using CTRL + M or in query option in menu bar*/
SELECT empid,name,deptid
FROM Employee
GO
SELECT empid,name,deptid
FROM Employee_View
GO
So execution plan clearly shows that sql server always use index on table
SQL - Filtered Index
ReplyDeleteFilter Indexes are most powerful feature of SQL because they provide opportunities for much more efficient use of I/O and they have great potential. A Filtered Index is an optimized non-clustered index which allows us to define a filter predicate with WHERE clause whenever creating the index. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Filter Index