ansaurus

Question

SQL Server Indexed View syntax for joined DB

Answer 1

+3 A:

In order to create an indexed view your base tables must reside within the same database.

Consult the following Microsoft Books Online reference for further details:

http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx

John Sansom 2009-08-12 10:42:14

thx for the answer. actually i need that index desperatly for performance reasons. is there any way to get a single field of that view indexed eventough it collects data from other 'places' too?

KB22 2009-08-12 10:43:34

@KB22: To answer your further query, unfortunately the answer is no. You see in order to create say a non clustered index on a view you first must create a Clustered Index. In order to do this SQL Server has to physically materialise the table data from the view and in doing so must validate the full contents of the view. This action cannot be performed when a view contains base tables that are distributed across separate databases.

John Sansom 2009-08-12 10:59:08

Further to my previous comments, I would suggest a good starting point would be to construct the cross database query that would satisfy your selection requirements and then review the execution plan. Ensure all your join predicates and search arguments have suitable indexes in place etc.

John Sansom 2009-08-12 10:59:57

@John: thx for the elaborate answer. I read more into it and solved the problem with a 'pre-located table' which contains the relevant data physically in the relevant database. Anyways, some more lessons learned. ;) k

KB22 2009-08-14 13:26:36

@KB22: You're very welcome. Glad to hear you got it sorted.

John Sansom 2009-08-14 15:06:06

ansaurus

tags:

views:

answers:

SQL Server Indexed View syntax for joined DB

related questions