SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alternative to views


Alternative to views

Author
Message
wanox
wanox
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 226
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18451 Visits: 6431
Using linked servers (http://msdn.microsoft.com/en-us/library/ms188279.aspx) you should be able to access tables directly given appropriate authorizations.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41530 Visits: 14413
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.

With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This

SELECT column_a
FROM dbA.dbo.table_a;



can be run in dbB to retrieve data.

SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18451 Visits: 6431
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.

With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This

SELECT column_a
FROM dbA.dbo.table_a;



can be run in dbB to retrieve data.

SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.


dbA can be a DB2 database? I didn't know that.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41530 Visits: 14413
dwain.c (11/15/2012)
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.

With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This

SELECT column_a
FROM dbA.dbo.table_a;



can be run in dbB to retrieve data.

SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.


dbA can be a DB2 database? I didn't know that.

Not straightaway but we could always define a SYNONYM named dbA.dbo.table that resolved to a remote table referenced by its 4-part name, however that's a different bunny trail.

There is an ambiguity in the original post, and I read "db2" as the name of a SQL Server database on the same instance as dbA. If "db2" actually means an instance of an IBM DB2 database to the original poster then Linked Servers are pretty much the only option if the requirement was to do everything in T-SQL. if that is the case then I would edit to replace "3-part naming" in my initial post with "4-part naming."

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
wanox
wanox
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 226
We are trying to stay away from linked servers. Alos we have a policy to to directly access the tables in the other databases and that is why we use views. Synonyms might not be a bad idea. I'd look into that, otherwise does anyone have an other tricks for databases to communicate.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222396 Visits: 42003
wanox (11/15/2012)
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.


As a couple of folks have pointed out, Synonyms are probably the best way to go here and can be pointed at either a local (same instance) database or a linked server database. There are two great things about synonyms... you don't need to remember to regen (SELECT *... a very bad thing) or rebuild (discreet named columns) views if columns are added to or deleted from the underlying tables and, as with views, you can avoid making your code dependent on another database by sticking to a 2 part naming convention.

I said "probably" because the bad part about Synonyms is that they can't hide columns or rows (if you need to) using just the Synonym like you can with a View.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wanox
wanox
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 226
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.

Thank you all for your responses!!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222396 Visits: 42003
wanox (11/18/2012)
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.

Thank you all for your responses!!


Be prepared. Like I said, the ONLY advantage that synonyms have over "pass through" views is that they never need to be regenerated if the underlying object (table, in this case) changes. Views have a lot more advantages (which I also covered) over Synonyms and if you need the type of functionality they offer, a Synonym just isn't going to hack it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
EdVassie
EdVassie
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14605 Visits: 3904
Why do you not want to use a view?

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search