adding intermediate views - good or bad design?

Question

adding intermediate views - good or bad design?

Andrew-443839

Valued Member

Points: 51
More actions
March 31, 2010 at 10:52 am

#231364

About to migration production environments, doing some preparation on the reporting side.
If I have tables in REALDATABASE
REALDATABASE.dbo.customers, with real data in it
REALDATABASE.dbo.sales, with real data in it
And some views in REPORTINGDATABASE (same server)
REPORTINGDATABASE.dbo.report1, which is a complex view pointing to customers and sales
REPORTINGDATABASE.dbo.report2, which is a complex view pointing to customers and sales
(...there are many of these)
Can I add new views:
REPORTINGDATABASE.dbo.customers as "SELECT * FROM REALDATABASE.dbo.customers"
REPORTINGDATABASE.dbo.sales as "SELECT * FROM REALDATABASE.dbo.sales"
(I won't actually use SELECT *, I will list the fields!)
...and repoint my complex views to these simple views.
...then later, if I need to rename REALDATABASE, I just have to modify these new "simple" views, and all the "complex" views (report1, report2, etc) will update.
Are there performance/design implications?
Many Thanks
Andrew

Viewing 14 posts - 1 through 13 (of 13 total)

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

No performance issue. This is right approch. You can keep the linked server name same even if I need to rename REALDATABASE. You do not need to change the simple views also.:-)

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

Using views is one approach - another approach is to use synonyms. For example:

-- First, create a schema for your synonyms. Not necessary, but organizes them for you

CREATE SCHEMA RealDB AUTHORIZATION dbo;

GO

-- Now, create your synonyms

CREATE SYNONYM RealDB.Customers FOR REALDATABASE.dbo.Customers;

CREATE SYNONYM RealDB.Sales FOR REALDATABASE.dbo.Sales;

...

Use the synonyms in your complex views

Now, if you need to move the database - rename it or whatever, just drop the synonym and recreate it.

DROP SYNONYM RealDB.Customers;

CREATE SYNONYM RealDB.Customers FOR NewREALDB.dbo.Customers;

DROP SYNONYM RealDB.Sales;

CREATE SYNONYM RealDB.Sales FOR NewREALDB.dbo.Sales;

...

You can also create synonyms for linked server objects, as in:

CREATE SYNONYM RealDB.Customers_Remote FOR RemoteServer.REALDB.dbo.Customers;

Again, move the linked server - change the db, etc... you just drop and recreate the synonyms.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

Andrew-443839 (3/31/2010)
...then later, if I need to rename REALDATABASE

Just out of curiosity... any particular reason you foresee the need of renaming REALDATABASE in the future?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Andrew-443839 Valued Member Points: 51 More actions · Answer 4

PaulB-TheOneAndOnly (4/1/2010)
Andrew-443839 (3/31/2010)
...then later, if I need to rename REALDATABASE
Just out of curiosity... any particular reason you foresee the need of renaming REALDATABASE in the future?

It's an ERP database going through a lengthy (all weekend) application upgrade process, which will be replaced with a (very similar) database with a different name.

Some of the reporting is based on some complex custom views sitting in another (empty) database on the same server. I want to simplify the reporting "switchover" as much as possible.

I'll probably go ahead with the "intermediate" views solution, thanks to everyone for the input.

Andrew

Paul White SSC Guru Points: 150457 More actions · Answer 5

Jeffrey Williams-493691 (3/31/2010)
Using views is one approach - another approach is to use synonyms.

I would tend to prefer the synonym approach - assuming the database in question is SQL Server 2005 or above (regardless of compatibility level).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 6

Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (3/31/2010)
Using views is one approach - another approach is to use synonyms.
I would tend to prefer the synonym approach - assuming the database in question is SQL Server 2005 or above (regardless of compatibility level).

I agree with you - that's why I suggested it. But, I am curious why you prefer this approach over using views.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Paul White SSC Guru Points: 150457 More actions · Answer 7

Jeffrey Williams-493691 (4/2/2010)
I am curious why you prefer this approach over using views.

Synonyms were created to meet exactly the sort of need presented here.

They are easy to define, and avoid ugly SELECT * constructions.

They also don't go wrong if the definition of the referenced object changes - no need to refresh views or schema-bind (which isn't possible anyway outside the same database).

To my mind, views were always a workaround solution - synonyms seem...well...nicer. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 8

Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (4/2/2010)
I am curious why you prefer this approach over using views.
Synonyms were created to meet exactly the sort of need presented here.
They are easy to define, and avoid ugly SELECT * constructions.
They also don't go wrong if the definition of the referenced object changes - no need to refresh views or schema-bind (which isn't possible anyway outside the same database).
To my mind, views were always a workaround solution - synonyms seem...well...nicer. 🙂

Exactly the same reasons I prefer them also. Thanks.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Paul White SSC Guru Points: 150457 More actions · Answer 9

Jeffrey Williams-493691 (4/2/2010)
Exactly the same reasons I prefer them also. Thanks.

Heh, so I passed? 😉 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 10

Paul White NZ (4/2/2010)
Jeffrey Williams-493691 (4/2/2010)
Exactly the same reasons I prefer them also. Thanks.
Heh, so I passed? 😉 😎

No - more like I passed. Just confirms my own reasons for using synonyms is all 😀

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Richellere SSC Eights! Points: 902 More actions · Answer 11

Hey, I like sysnonyms, one of the things I always wanted SQL Server to take from ORACLE, and now it's done.

Regards

Richellère

Andrew-443839 Valued Member Points: 51 More actions · Answer 12

As a (rather late) post script to the thread, in the end we did use synonyms, with a successful outcome. 🙂

Thanks again.

Andrew

Paul White SSC Guru Points: 150457 More actions · Answer 13

Andrew-443839 (7/5/2010)
As a (rather late) post script to the thread, in the end we did use synonyms, with a successful outcome. 🙂
Thanks again.
Andrew

Good to hear, thanks for the update 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi