What is the difference between oracle and sql server

Difference: 1. Oracle does not automatically submit by default, while Sql Server automatically submits by default; 2. Oracle fully supports transactions, and Sql Server can only support transactions when it is not automatically submitted; 3. Oracle does not lock during logical backup Data, data must be locked during Sql Server logical backup, etc.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

The difference between oracle and sql server

• Affiliated company:

Oracle is a set of core software products developed by the American ORACLE company (Oracle);

SqlServer is jointly developed by Microsoft, Sybase and Ashton-Tate, and currently belongs to Microsoft;

• Flexibility of sql statements:

Oracle feels more stable and traditional about sql statementsSome;SqlServer is very good at sql statements. Two idioms summarize it:

Down to earth, step by step;

• Persistence of saving data:

Oracle writes the submitted sql operation line into the online log file and saves it to the disk can be restored at any time;

SqlServer also has full persistence and delayed persistence features since 2014;

##Submission method:

• Oracle does not automatically submit by default and requires users to manually submit (commit);

Sql Server defaults to automatic submission and users can manually stop (KILL);

Support for transactions:

• Oracle fully supports transactions;

SqlServer in non- Transactions can only be supported when automatically committed;

Support for concurrency:

• Oracle uses rows Level lock, the granularity of resource locking is much smaller, and it does not rely on indexes, and the support for concurrency is much better, that is,

"optimistic concurrency control"

； SqlServer controls

shared locks, exclusive locks, and update locks

on tables, table paginations, index keys, and individual data rows. Simply put: use shared locks when reading , use exclusive locks when modifying, and update locks are a mixture of shared locks and exclusive locks;

Logical backup:

• Oracle does not lock data during logical backup, and the backed up data is consistent;

SqlServer needs to lock data during logical backup to ensure that the backed up data is consistent, which will affect normal business ;

Copy backup:

• Oracle has both push or pull traditional data replication, as well as Dataguard's dual-machine or multi-machine disaster recovery mechanism, as well as the mature hot backup tool rman, can automatically switch the backup database to the main database when a problem occurs in the main database.

But the configuration management is more complicated

；A copy-only backup of SQL Server is a SQL Server backup that is independent of the regular SQL Server backup sequence. Typically, taking a backup

changes the database and affects how subsequent backups are restored

. Use only for: Backups performed for special purposes without affecting the overall backup and restore process of the database.

Performance diagnosis:

• Oracle has various mature performance diagnosis and tuning tools that can achieve Many automatic analysis and diagnostic functions. For example,

awr, addm, sqltrace, tkproof, etc.

; SqlServer performance testing tools include

sql server profiler

, performance Tuning tools include Database Engine Optimization Consultant, which is simple, easy to use and cool;

Permissions and security:

• Oracle's user and root user permissions are separated from each other. The permissions and security concepts are relatively traditional and quite satisfactory;

SqlServer can be logged in with windows authentication locally, and remotely. Use SqlServer to verify login (i.e. sa login);

Related services:

• MySQL is lightweight Type database, and free

Open source

, no service to restore data;Oracle is a heavy-weight database, there is a fee, Oracle company has any services for the Oracle database;SqlServer is a heavy-weight database Type database, charging (cooperating with Java), and also supporting suppliers;

Summary:

Oracle advantages

: Easy to use, powerful functions, good reliability, good security, good portability, adaptable to high throughput, suitable for various large, medium, small and microcomputer environments.

SqlServer Advantages

: Graphical user interface, rich programming interface tools, fully integrated with Windows NT, supports distributed partition view, suitable for Win's Web Development of technology. Recommended tutorial: "
Oracle Tutorial

"

The above is the detailed content of What is the difference between oracle and sql server. For more information, please follow other related articles on the PHP Chinese website!