SQL Server 2008 posibilities of having views from one database in another database?

Question

I have a vendor database from I want to extract and transform certain information. As I dont want to change the structure of the original database due to licensing I therefor wondering if I can put together views from the vendor database but storing those in a second sql server 2008 database together with some other replicated and transformed data. The second database is supposed to live beside the first one on the same server.

Is it possible to have views like this and if so, do I loose a lot of performance when querying the views?

Answer 1

Licensing shouldn't stop you adding views to your first database. Every place I've been in has custom objects for reporting etc that extend the functionality of the 3rd party product.

Saying that, it is possible to have 3 part object names in views in your own database

CREATE VIEW myview 
AS
SELECT ... FROM TheirDB.dbo.TheirTable
GO

Answer 2

Yes it possible, that's what three part naming is for.

Use the form: [DatabaseName].[DatabaseOwner].[TableName] when referencing your vendor tables. ie:

CREATE View [ViewName]
AS
SELECT * FROM [VendorDB].[dbo].[TableName]

Regarding performance, there's no big performance impacts, if the table has appropriate index and statistics these will be used by your queries in your DB.

Note: If you specify an index hint in your query, these will be ignored unless you bypass the view and refer to the table directly.

Location of your DB
As Stan noted, the above assumes that the two databases are residing on the same SQL Server instance, if you want to have a view residing in a different SQL instance (and I do not recommend this), you can:

1. Setup a linked server to the Vendor DB
2. Use four part naming in your view definition: [LinkedServerName].[DatabaseName].[DatabaseOwner].[TableName]

ie:

CREATE View [ViewName]  
AS  
SELECT * FROM [VendorDBInstance].[VendorDB].[dbo].[TableName]