• vinothraj (9/23/2010)


    Select

    EmployeeNumber,

    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.