I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server.
There is a SQL 2008 R2 database server that exposes all of the tables from the 2005 databases using views (and only views) within a single database. There is no row-level filtering done on these views, they contain the same data as the original tables.
Regardless of why this architecture was chosen, it is causing three specific problems:
-
In SQL Server Management Studio, when browsing the views, there is no way to determine the indexes or primary keys on the base table.
-
When using tool such as SSRS to build a drag-and-drop type report, it does not automatically generate the relationship between the views (probably linked to #1).
-
Running an execution plan against a query written against the views is virtually useless as many of the nodes end up being "Remote Query"
I have done a little research into Indexed Views. The idea would be to basically replace the existing views with indexed views that have the same unique index as the base table. However, I don't think this is possible due to the following:
All base tables referenced by the view must be in the same database as the view and have the same owner as the view. http://msdn.microsoft.com/en-us/library/ms191432.aspx
Does anyone have any thoughts on how to alleviate the above three problems?
Best Answer
You are correct you cannot use indexed views. Sadly what you have here is a business problem not a database problem. If you need to know the PKs and indexes from the other database and you don't have even viewing access, then require the DBAs who do have that access to provide the information you need to do your job or require them to write and performance tune the queries. No one should be writing queries against a database they do not have access to. If they won't give you access, then the work belongs to them too. I would have my boss talk to their boss and ask them to solve this set of problems for you. They created the problem it's up to them to fix it.