join or subselect: databases

Showing posts with label databases. Show all posts

Friday, March 23, 2012

Joins Across Servers/Databases

I am trying to determine how best to create a query that will allow me to perform 'joins across servers'.

I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.

From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.

Any thoughts ?Hello,

the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.

If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.

Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.

Hope that helps ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Wednesday, March 21, 2012

Joining tbls from different db's with WHERE statement

Hi,
This is the first time I've joined tables residing on seperate databases. It
works without the WHERE statement, but when I add the WHERE clause in, it
returns no rows. I am certain the argument is correct & have used it in a
simple table and it returns rows. Is there something specal about doing this
against two tables from different DB's
Thanks for any clues.
Ant
SELECT i.Log_id,
e.name,
e.tel,
e.email,
i.TimeSubmit,
i.Priority,
i.Status,
i.ProDes,
i.emp_id
FROM IDB.dbo.IRL i
INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'Could it be that only a small number of records in table IRL have a status
of open, by INNER JOINing to Employee you eleminate the small number. Try
this to find out...
SELECT i.Log_ID, i.Status , i.emp_id, e.emp_id
FROM IDB.dbo.IRL i
LEFT JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'
HTH. Ryan
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A82B8A64-7986-417D-B34F-3B6EE50928C2@.microsoft.com...
> Hi,
> This is the first time I've joined tables residing on seperate databases.
> It
> works without the WHERE statement, but when I add the WHERE clause in, it
> returns no rows. I am certain the argument is correct & have used it in a
> simple table and it returns rows. Is there something specal about doing
> this
> against two tables from different DB's
> Thanks for any clues.
> Ant
> SELECT i.Log_id,
> e.name,
> e.tel,
> e.email,
> i.TimeSubmit,
> i.Priority,
> i.Status,
> i.ProDes,
> i.emp_id
> FROM IDB.dbo.IRL i
> INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
> WHERE i.Status = 'Open'

Joining tables in different databases

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

ThanksTry 'four part names' :).
server.database.owner.table

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
> Hi
> I'm working on an ASP project where the clients want to be able to
> effectively perform SELECT queries joining tables from two different
> databases (located on the same SQL-Server).
> Does this involve creating virtual tables that link to another database,
> or
> am I completely on the wrong track?
> Any hints as to where I might find more information (buzz-words, etc.)
> would
> be most appreciated.
> Thanks|||Use 3-part naming:

select
*
from
dbo.MyTable l
join
OtherDB.dbo.OtherTable o on o.PK = l.PK

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
Hi

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

Thanks|||The OP said the two DB's were on the same server. Therefore, 3-part naming
is sufficient.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5cc8$bue$1@.magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table

It sure is! I've just tried it out. Where I went wrong was thinking that
2-part naming would do it (omitting the 'dbo').

Thanks, Tom|||Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
> The OP said the two DB's were on the same server. Therefore, 3-part
> naming
> is sufficient.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
> Try 'four part names' :).
> server.database.owner.table
>
> MC
>
> "Captain Nemo" <nemo@.nospam.com> wrote in message
> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>> Hi
>>
>> I'm working on an ASP project where the clients want to be able to
>> effectively perform SELECT queries joining tables from two different
>> databases (located on the same SQL-Server).
>>
>> Does this involve creating virtual tables that link to another database,
>> or
>> am I completely on the wrong track?
>>
>> Any hints as to where I might find more information (buzz-words, etc.)
>> would
>> be most appreciated.
>>
>> Thanks
>>
>>|||There's often a performance difference.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:dRsBf.4433$ft2.115520@.news20.bellglobal.com.. .
> There's often a performance difference.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
> Agreed, but why not provide a more complete info since theres a little
> difference? He may need to pull data from two servers tomorrow...
>
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
>> The OP said the two DB's were on the same server. Therefore, 3-part
>> naming
>> is sufficient.
>>
>> --
>> Tom
>>
>> ----------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>>
>> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
>> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
>> Try 'four part names' :).
>> server.database.owner.table
>>
>>
>> MC
>>
>>
>> "Captain Nemo" <nemo@.nospam.com> wrote in message
>> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>>> Hi
>>>
>>> I'm working on an ASP project where the clients want to be able to
>>> effectively perform SELECT queries joining tables from two different
>>> databases (located on the same SQL-Server).
>>>
>>> Does this involve creating virtual tables that link to another database,
>>> or
>>> am I completely on the wrong track?
>>>
>>> Any hints as to where I might find more information (buzz-words, etc.)
>>> would
>>> be most appreciated.
>>>
>>> Thanks
>>>
>>>
>>
>>
>>
>|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Do you mean that actually specifing servername slows down the query? Could
> you explain why?

If the server name use is @.@.servername, SQL Server will shortcut, and
there is no overhead.

But if the linked server is defined a true loopback, so that there is a
new connection made, there is obviously an overhead, as data is first
passed to SQL Server, to the OLE DB provider on one connection, and then
the OLE DB provider passes the data back to another connection.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for that, I assumed engine would 'optimize' that and never
checked. Just when I think I actually know something ;)....

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9756F1F84A0F4Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
>> Do you mean that actually specifing servername slows down the query?
>> Could
>> you explain why?
> If the server name use is @.@.servername, SQL Server will shortcut, and
> there is no overhead.
> But if the linked server is defined a true loopback, so that there is a
> new connection made, there is obviously an overhead, as data is first
> passed to SQL Server, to the OLE DB provider on one connection, and then
> the OLE DB provider passes the data back to another connection.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Thank you for that, I assumed engine would 'optimize' that and never
> checked. Just when I think I actually know something ;)....

As I mentioned, it does optimize when the server is @.@.servername, which
it can recognize. But it does not analyse connection strings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 12, 2012

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||UÅ¼ytkownik Tibor Karaszi napisaÅ?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> UÅ¼ytkownik Tibor Karaszi napisaÅ?:
>> 1 is same as 2. The optimizer has all the information and can process the query the same whether
>> the tables are in the same database or different database. For 3 and 4, the query is optimized
>> locally and parts of the query is passed onto the linked server. This limits the flexibility that
>> the optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test
>> with your data, schema, queries etc.
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Friday, March 9, 2012

Joing tables from different databases - performance issues

join with table on another database not working

Hi,

I have two databases on the same server: COMU0002 and 2007DU
and two tables: tecnicos on COMU0002 and telf_cli ON 2007DU

Being on COMU0002 this query doesn't work:

SELECT * FROM tecnicos
LEFT JOIN 2007DU.dbo.telf_cli
ON ...

The error says: INCORRECT SYNTAX NEAR '2007'

Even this query being on 2007DU fails on management studio:
SELECT * FROM 2007DU.dbo.telf_cli

but SELECT * FROM telf_cli works ok

Any ideas?
Thank you!Santi (santi.serrano@.gmail.com) writes:

Quote:

Originally Posted by

I have two databases on the same server: COMU0002 and 2007DU
and two tables: tecnicos on COMU0002 and telf_cli ON 2007DU
>
Being on COMU0002 this query doesn't work:
>
SELECT * FROM tecnicos
LEFT JOIN 2007DU.dbo.telf_cli
ON ...
>
The error says: INCORRECT SYNTAX NEAR '2007'
>
Even this query being on 2007DU fails on management studio:
SELECT * FROM 2007DU.dbo.telf_cli
>
but SELECT * FROM telf_cli works ok

2007DU does not adhere to the rules for regular identifiers, as they
must start with a letter, $ or a hash mark. An identifier cannot start
with a number.

However, in difference to most other languages, SQL permits you to use
about any string as an identifier, if you quote it. So instead say one of:

SELECT * FROM "2007DU".dbo.telf_cli
SELECT * FROM [2007DU].dbo.telf_cli

The former agrees with the ANSI standards, but the brackets is what
is most commonly used in the SQL Server world.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It works! Thank you!

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two tables from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue it is urgent
You can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two table
s from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue
it is urgentYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

join two different databases where they r in different servers

i am fresher i want to know write one query where i want to select two tables from two different databases that are in different servers
eg : it is first server detail
sqlserver1 -servernam
EmployeDet -Database Nam
Emp_Mas - table nam
colums - empno, empname,....
it is second server detail
sqlserver2 -servernam
IMS- Database Nam
IMSTable - table nam
Colums- empno,locid,extno.....
by combining above two databases i want details please help me in this issue it is urgenYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>

Wednesday, March 7, 2012

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato Pierantonio
Yes, just define your tables in the form of <database name>.<owner
name>.<table name>
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato PierantonioYes, just define your tables in the form of <database name>.<owner
name>.<table name>
--
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

JOIN Tables in 2 Databases

It is possible make a View that Join 2 tables in different databases?
Thanks
Bonato PierantonioYes, just define your tables in the form of <database name>.<owner
name>.<table name>
Jacco Schalkwijk
SQL Server MVP
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:ufz5N6YzEHA.748@.TK2MSFTNGP14.phx.gbl...
> It is possible make a View that Join 2 tables in different databases?
> Thanks
> Bonato Pierantonio
>

Join tables from two databases

Say I have two database files, database1.sdf and database2.sdf, how can I make a select that joins tables from both?

I'm using C#.

Thanks!

There is no implicit support for querying across multiple databases with the SQL Server CE engine itself. However you could achieve a similar result by loading data from the two databases into an in-memory dataset from which you could then query the data.

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1datetime=null,
@.Date2datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:514309300509D@.droptable.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns

> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode =
> city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.

> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).
|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.droptable.com

Friday, March 23, 2012

Joins Across Servers/Databases

Wednesday, March 21, 2012

Joining tbls from different db's with WHERE statement

Joining tables in different databases

Monday, March 12, 2012

Joining across databases

Joining across databases

Joining across databases

Joing tables from different databases - performance issues

Joing tables from different databases - performance issues

Joing tables from different databases - performance issues

Joing tables from different databases - performance issues

Friday, March 9, 2012

Joing tables from different databases - performance issues

join with table on another database not working

join two different databases where they r in different servers

join two different databases where they r in different servers

join two different databases where they r in different servers

Wednesday, March 7, 2012

JOIN Tables in 2 Databases

JOIN Tables in 2 Databases

JOIN Tables in 2 Databases

Join tables from two databases

Join tables across databases?

join or subselect

Blog Archive

About Me