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

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?

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:

• Setup a linked server to the Vendor

DB

• Use four part naming in your view definition: [LinkedServerName].[DatabaseName].[DatabaseOwner].[TableName]

ie:

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

StackExchange Database Administrators Q#3229, answer score: 10