Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Alternative to views Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 8:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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.
Post #1385434
Posted Thursday, November 15, 2012 8:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 3,627, Visits: 5,272
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!
Post #1385440
Posted Thursday, November 15, 2012 10:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1385463
Posted Thursday, November 15, 2012 10:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 3,627, Visits: 5,272
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!
Post #1385469
Posted Friday, November 16, 2012 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1385711
Posted Saturday, November 17, 2012 6:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
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.
Post #1385937
Posted Saturday, November 17, 2012 2:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386009
Posted Sunday, November 18, 2012 3:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:48 AM
Points: 34, Visits: 224
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.

Thank you all for your responses!!
Post #1386107
Posted Monday, November 19, 2012 6:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386335
Posted Monday, November 19, 2012 6:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 2,868, Visits: 3,213
Why do you not want to use a view?

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1386358
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse