Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2009-01-21 : 05:45:19
|
| Running SET SHOWPLAN_XML ON has confirmed what I sensed was the case: if I am in database X and I create a view to a table in database Y, the indexes from the table in database Y are not exposed - showplan generates a 'missing indexes' section, but the fields it recommends for an index are actually indexed in the source table.When a view is to a table in another database, can the indexes that are present be used in the view?I am using sqls 2005 x64 Developer; sp2.Thanks.Chris |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-21 : 11:35:14
|
| I created a test view joining 2 tables across different databases (both on my local PC). The view used any combination of indexes, (1 index on same db, 1 index on other db, 2 indexes across 2 dbs).You could try using an index hint inside the view.e.g.Select * from TableAWITH (INDEX(inxTableA_ID)) |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 11:54:17
|
| Why not take advantage of indexed view? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-21 : 13:17:19
|
| >>When a view is to a table in another database, can the indexes that are present be used in the view?The answer to this is definately yes. So I suspect there is something else going on here.>>Why not take advantage of indexed view?Indexed views may be a nice tool in the sql server toolbox but I wouldn't use it mask whatever problem is preventing the use of existing indexes.btw, indexed views have a lot of restrictions and if you haven't designed your system with them in mind they can be very frustrating to implement. Do you use indexed views sodeep?Be One with the OptimizerTG |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 14:11:12
|
| I agree that there are lots of requirement for it and it has to be created with schemabinding as well. Yes,We are using it and we have doubled up the performance of some views with it. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-21 : 14:19:04
|
| thanks for the info! I think you are only the second sqlteam contributor I've heard of that have indexed views implemented in their system (with good result).Be One with the OptimizerTG |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-21 : 14:22:31
|
| There is very good whitepaper on it:http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2009-01-26 : 05:39:47
|
| Thanks for these suggestions - I will give them a try.Chris |
|
|
|
|
|
|
indexing a view