Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
add comment

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]
share|improve this answer
    
The second DB also has to be on the same instance of SQL on the server. –  StanleyJohns Jun 8 '11 at 13:21
    
We do this all the time and works like a champ. –  Cape Cod Gunny Jun 8 '11 at 14:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.