Differences between SQL Server and Oracle from an application perspective

Many software companies understand the importance of developing applications that do not depend on specific database types (such as Oracle, SQL Server, and DB2), allowing customers to choose their preferred platforms. Generally, software developers can identify their customers responsible for database maintenance and must use existing platforms and personalized customers.

There have been many articles describing the differences between Oracle and SQL Server from the perspective of the enterprise and the database administrator. This article describes the differences between SQL Server and the Oracle platform from the application perspective, and discusses several possible methods for developing applications that do not depend on the database environment.

At the same time, I will not discuss the obvious differences between the two platforms for applications, such as table segmentation and indexing.

Define common interfaces and languages

There are a few common languages and interfaces that allow applications to not rely on databases. They can also be applied to relational databases in the same way:

ANSI is defined by the US National Institute of Standards and is a volunteer member organization (operated by private funds) that develops nationally recognized standards in a wide range of areas such as equipment and procedures. In the database field, ANSI defines the standard for writing SQL commands. Assume that commands can run on any database without changing the syntax of commands.

ODBC is an open database connection (ODBC) interface defined by Microsoft. It allows applications to access data in the database management system (DBMS) and uses SQL as the standard for data access. ODBC allows maximum interconnectivity, which means that a single application can access different database management systems. Then, application end users can add an Open Database Connection (ODBC) database driver to link the application to their selected database management system.

OLEDB, the successor of ODBC, is a group that allows any connection similar to SQL Server, Oracle, DB2, and so on based on VB, C ++, and Access, mySQL and other background "front-end" software components. In many cases, the OLEDB component provides much better performance than the original ODBC.

JDBC (Java database connection) application interface is a Java programming language and a wide range of databases, between SQL database and other table column data sources (such as workbooks or common text files, conducts industry standards that do not rely on database connections. The JDBD application interface provides a call-level application interface for SQL-based database access.

Common interfaces in the real world

Unfortunately, not all database-level commands are ANSI, and each database platform has its own scaling function. ANSI, or a common interface, generally represents several functions, which may also mean the loss of performance competitiveness. For small databases and small applications, it is easy to maintain general access to the database, but when the database and/or applications become larger and more complex, you have to add features to the code.

Example:

Commands written in the same way on the two platforms:

Insert into Table_1 values (1,'Michelle')Update Table_2 set Col_1 = 2Delete from Table_3 where Col_3 like 'Michelle%'

On the two platforms, commands are not written in the same way:

Select getdate() Select case Fld when 1 then 'a' When 2 then 'b' Else 'c' EndFrom Table_4OracleSelect sysdate from dualSelect DECODE (Fld, 1, 'a', 2, 'b', 'c')From Table_4

The following two articles cover a list of comparisons between Oracle PL/SQL commands and T-SQL commands:

· Porting from Oracle to SQL Server

· Start to learn SQL: difference between SQL Server and Oracle

Possible solutions

I have seen several possible solutions to database interoperability problems:

Which solution is selected?

The answer to this question depends on the features and platform of the application. Every solution is easy to implement, and there is no best solution here.

Conclusion:

If you want to develop your application into a database independent, you should plan the solution carefully. Considering the complexity of applications at the database level, there is also the total amount of code required. In the planning process, it is crucial to take into account the future development of the application.

(