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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-02 : 11:01:30
|
| Nithi writes "HiIs it possible to create index views in database A by accessing tables that are in database B?thanks" |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 11:18:53
|
| Welcome to SQL Team Nithi!VIEWs certainly, I don't see why they shouldn't be indexed.Kristen |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 11:37:26
|
quote:
Originally posted by Kristen
Welcome to SQL Team Nithi!VIEWs certainly, I don't see why they shouldn't be indexed.Kristen
According to BOL you cant. BOL: "Tables and user-defined functions must be referenced by two-part names. One-part, three-part, and four-part names are not allowed."Two-part naming is [Owner].[Object] which is the only naming that is allowed. Three-part is [Database].[Owner].[Object] and is not allowed.So if you attempt to use a view on a table in a different database then you would need to use Three-part naming which violates the rules of an Indexed view.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 11:41:14
|
| Yup, I should have read BoL too:"Creating an Indexed View : Requirements for the View...All base tables referenced by the view must be in the same database as the view and have the same owner as the view."VIEW on its own to another database will be fine ...Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-02 : 11:47:39
|
quote:
Originally posted by Kristen
Yup, I should have read BoL too:"Creating an Indexed View : Requirements for the View...All base tables referenced by the view must be in the same database as the view and have the same owner as the view."VIEW on its own to another database will be fine ...Kristen
I wonder if the owner rule still applies if you have enabled the cross database ownership chaining option that SP3 offers? I've never seen that option used so I wouldnt know what it does.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-02 : 13:18:47
|
| Do you think its the Schema Bind requirement that is imposing the "current database" requirement?Kristen |
|
|
|
|
|
|
Indexed views