Menu
I am in a state of confusion that what are the advantages and disadvantages of using cross database queries within the same SQL Server instance?
One thing I can think of is the performance issue (queries will be slow) if that the case what is the solution of this problem?
Kindly highlight some advantages and disadvantages with solution so that I can work on the two database confidently .
I have two databases db1 Companies/CRM and db2 Products/E-commerce
519
Disadvantages of Subquery:The optimizer is more mature for MYSQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as join. We cannot modify a table and select from the same table within a subquery in the same SQL statement.
A cross database join allows you to join data from two different types of databases as if they were in the same database.
The CROSS JOIN is used to show every possible combination between two or more sets of data. You can do a cross join with more than 2 sets of data. Cross Joins are typically done without join criteria.
In SQL, the CROSS JOIN is used to combine each row of the first table with each row of the second table. It is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.
If you will read article here:https://dba.stackexchange.com/questions/692/sql-server-2008-cross-database-performance-on-same-physical-machine-and-server you will realize that you will not have performance penalty because query optimizer will be only one. Difference will be only if you will be use cross-instance queries.In this case, query will be analyzed by separate query analyzers on both instances.
Possible issues that can be in your case are related to management.Read how to manage cross-database ownership here: http://msdn.microsoft.com/en-us/library/ms188676.aspx
72
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
