Post reply

How to create a index view for a table residing on other database

  • August 19, 2011 at 10:40 am

    #241918

    Hi Can some some help me in creating a index view on a table residing on different database .Please find my query as below and i was not able to create the index view .So is it possible to create a index view in the following scenario ?

    create view [dbo].[U_VALUEView] with schemabinding as

    (

    SELECT column1,column2,column3,column10

    FROM [Test1].[dbo].[U_VALUE]

    );

    create unique clustered index IX_U_VALUEView on [dbo].[U_VALUEView](column1)

  • August 19, 2011 at 10:52 am

    #1370902

    can't be done.

    by definition, an indexed view creates an index on an object in the database, so it becomes materialized as if it were a table, and an index must reference the object within the database.

    you can create a non-indexed view on that other database's table, or consider copying the data(or a subset of what you really need) into the local database isntead.

    you could also create a Synonym for the table in the oteher database as well.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 19, 2011 at 1:34 pm

    #1370961

    so can we create the non clustered index in my case then ?I have a table in database1 and i created the view in database2 for table in database1. So in database 2 the view can have non clustered ?

  • August 19, 2011 at 2:01 pm

    #1370964

    nope, no non-clustered indexes either

    indexes must always point to an object within a given database.....but the original table in the original database can have indexes up the wazoo. if they are on the same server, then a database call to otherDatabase.dbo.Tablename should perform pretty well;

    if it's on a different server, than performance will probably be slow.

    I assume you are asking this because performance is bad? if you can show the actual execution plan of a query, we can certainly help you look at it, and suggest indexes or changes that will make it perform well.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 19, 2011 at 2:12 pm

    #1370973

    Why do you want to do this?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 19, 2011 at 2:18 pm

    #1370976

    Hi wanted to do this as my boss asked me to do the following....

    "test to see we can add an index in a different database then the prime table and test creation of an indexed view in a different database to prove it “materializes” the view in order to index it."

  • August 19, 2011 at 7:09 pm

    #1371004

    Post removed.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 20, 2011 at 3:35 am

    #1371026

    RamSteve (8/19/2011)

    Hi wanted to do this as my boss asked me to do the following....

    "test to see we can add an index in a different database then the prime table and test creation of an indexed view in a different database to prove it “materializes” the view in order to index it."

    Then you can reply to him that no, you can't do either. Indexes live with their tables and indexed views can't be created across databases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply