CODEWITHSUNDEEP
sql-serverdatabaseoracle-databasesql-server-2012
mindbdev
mindbdev

Reputation: 444

Getting Data from an Oracle database to SQL Server

I am a SQL Server database developer. We have a current requirement that we need to link our product with an existing application of a client. Our product has a SQL Server 2012 database while the client's existing application uses Oracle 11g. Since the time limit for the project completion is limited we cannot migrate our application to Oracle.

The requirement is that we have to get the customer details from the Oracle database to do billing activities in our system.

So I went through a few links and found that SQL Server linked server can be used to do this. I have successfully created a view which uses the Customer table from the Oracle database using a linked server. It will solve our problem.

Now here are my questions:

Thanks in advance

Upvotes: 0

Views: 102

Answers (1)

davek
davek

Reputation: 22895

One drawback to consider is that the filtering on your view may take place at "your" end, rather than in Oracle. e.g. if you have a linked server (using, say, an OPENQUERY statement) and a view based on that and you do this:

select id from myView where id = 4711

expecting that the result will be very quick (assuming id is indexed etc.etc.), then you may be in for a shock as what will actually happen is:

  • the entire contents of the Oracle table are passed to SQL Server
  • SQL Server then filters this data, i.e. the filtering cannot be "pushed down" into the view objects (as they are remote).

N.B.: I know there are two ways to define linked server (openquery and the other one, I forget the details), so this may not always apply, but you should be aware of the potential performance hit.

Upvotes: 2

Related Questions