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

Question

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

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
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)

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 2

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 ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

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/

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 5

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."

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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