SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Alternative to Views
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lupison
Starting Member

5 Posts

Posted - 08/25/2010 :  12:20:05  Show Profile  Reply with Quote
Here's the basic problem.

I have 2 DBs. One contains a table. "DB1" & "DB2" DB2 can either reference D1.Table1 in it's stored procs etc, or it can choose to "override" that table and create that table inside itself (DB2.Table1) and use that in it's stored procs etc.

Other than a view, which would be horrendous in this case since the data is not going to be even close to being static, I was wondering if anyone knew of anything else I could use.

Or are views against a single table an "ok" thing now to do for a table with constantly changing data?

Basically I just need some sort of "pointer" object that when a stored proc calls it it points to the right table without the programmer having to change 100 store procs when it's decided the DB2 will override the table in DB1.

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 08/25/2010 :  12:38:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
Maybe what you want is a synonym.

quote:

Or are views against a single table an "ok" thing now to do for a table with constantly changing data?



Views can reference multiple tables via joins, etc... and have constantly changing data and still be fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lupison
Starting Member

5 Posts

Posted - 08/25/2010 :  12:49:39  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Maybe what you want is a synonym.

quote:

Or are views against a single table an "ok" thing now to do for a table with constantly changing data?



Views can reference multiple tables via joins, etc... and have constantly changing data and still be fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



I was always told that Views "recompile" thier data everytime a row changes in a table they reference. So that you only want Views against tables with very static data.
Go to Top of Page

Lupison
Starting Member

5 Posts

Posted - 08/25/2010 :  12:52:43  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Maybe what you want is a synonym.

quote:

Or are views against a single table an "ok" thing now to do for a table with constantly changing data?



Views can reference multiple tables via joins, etc... and have constantly changing data and still be fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



synonym appear to be perfect. Never heard of these before, they look very awesome even if I don't use them here.

thank you.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36565 Posts

Posted - 08/25/2010 :  13:33:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
A view needs to be recompiled if you are using select * in it and the schema changes.

There is no performance penalty of a constantly changing table inside a view. We use them quite heavily in one of my busiest systems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

niechen861102
Starting Member

9 Posts

Posted - 09/06/2010 :  23:05:18  Show Profile  Reply with Quote
spam removed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000