vinothraj (9/23/2010)
SelectEmployeeNumber,
EmployeeName
From
dbSecond.dbo.EmployeeTable
I want to create a index (either clustered or non-clustered) for this view as my view containing millions of records and i dont want to wait for the retrieval. If i want to create index for this view which is located in dbFirst referring dbSecond, i'm unable to do. Could you suggest me whether there is any alternate way to achieve this?
First, a view is an abstraction not a physical object meaning a view is just a stored query therefore it can contain nothing.
SQL Server engine takes advantage of the indexes pointing to the base table/s of the view.
You can see it by yourself doing this simple test.
1- Go to Northwind and create a view pointing to Pubs database like...
USE Northwind
go
SET ANSI_NULLS,QUOTED_IDENTIFIER ON
go
CREATE VIEW dbo.MyEmployeeView
AS
SELECT *
FROM pubs.dbo.employee
WHERE emp_id = 'JYL26161F'
2- Check the execution plan of query below...
USE Northwind
go
select *
from dbo.MyEmployeeView
... which should show something like...
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
select * from dbo.MyEmployeeView110[NULL][NULL]1[NULL]1[NULL][NULL][NULL]0.0126592[NULL][NULL]SELECT0[NULL]
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[employee]))131Bookmark LookupBookmark LookupBOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[employee])[employee].[hire_date], [employee].[pub_id], [employee].[job_lvl], [employee].[job_id], [employee].[lname], [employee].[minit], [employee].[fname], [employee].[emp_id]10.006251.1e-006490.0126592[employee].[hire_date], [employee].[pub_id], [employee].[job_lvl], [employee].[job_id], [employee].[lname], [employee].[minit], [employee].[fname], [employee].[emp_id][NULL]PLAN_ROW01
|--Index Seek(OBJECT:([pubs].[dbo].[employee].[PK_emp_id]), SEEK:([employee].[emp_id]='JYL26161F') ORDERED FORWARD)143Index SeekIndex SeekOBJECT:([pubs].[dbo].[employee].[PK_emp_id]), SEEK:([employee].[emp_id]='JYL26161F') ORDERED FORWARD[Bmk1000]10.00632857.96e-005530.0064081[Bmk1000][NULL]PLAN_ROW01
... it does takes advantage of existing base table indexes, doesn't it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.