Oracle and SQLserver

Difference between Oracle and SQLserver. I checked it online, roughly as follows. 1. In terms of platform adaptability. Oracle supports multiple operating systems, while SQL Server only supports windows. 2. From the perspective of database usage. Oracle gives dba more flexibility. You can adjust parameters based on actual conditions to achieve the best performance, but the disadvantage is that

Difference between Oracle and SQLserver. I checked it online, roughly as follows. 1. In terms of platform adaptability. Oracle supports multiple operating systems, while SQL server only supports windows. 2. From the perspective of database usage. Oracle gives dba more flexibility. You can adjust parameters based on actual conditions to achieve the best performance, but the disadvantage is that

Difference between Oracle and SQLserver. I checked it online, roughly as follows.

1. In terms of platform adaptability.

Oracle supports multiple operating systems, while SQL server only supports windows.

2. From the perspective of database usage.

Oracle gives dba more flexibility. You can adjust parameters based on actual conditions to optimize application performance, but the disadvantage is that it is difficult to get started. On the contrary, SQL Server is easier to use.

3. database system architecture.

The file architecture of Oracle is as follows:

Data File:. dbf (real data ).

Log File: rdo

Control File: ctl

Parameter file: ora

The file architecture of SqlServer is as follows:

. Mdf (data dictionary)

. Ndf (data file)

. Ldf (Log File)

4. In terms of storage structure.

Oracle storage structure:

There are two parameter blocks in Oracle: pctfree (fill factor) and pctused (reuse factor), which can be used to control when the block exists, when there is not enough space to accept new information (Block Storage analysis mechanism ).

This reduces the possibility of Row-based data migration. You can set the block size (olp block and dss block ).

In Oracle, continuous Block Composition areas can be dynamically allocated (the distribution of zones is equal or can increase by itself) to reduce the number of space allocations.

In Oracle, tables can be divided into multiple segments, which are composed of multiple partitions. Each segment can be specified in which tablespace (the types of segments are divided: data Segment, index segment, rollback segment, temporary segment, cash segment ). Oracle can also partition tables and physically separate data on disks according to user-defined business rules, conditions, or specifications. This greatly reduces the possibility of disk contention.

Oracle has seven empty basic tablespaces:

. System tablespace (stores the information required by data dictionary and data management ).

. Temp temporary tablespace.

. Tools interactive tablespace.

. Users user default tablespace.

. Indx index tablespace.

. Dbsys Fu data table space

Different data is stored in different tablespaces. in Oracle, the base table is encrypted and cannot be accessed by anyone. It can only be viewed in the user's Visual View.

SqlServer storage structure:

The minimum allocation unit is page size. Each page is 8 K. eight consecutive pages can be formed into an "extension" to further reduce the resources consumed during allocation. Data in SqlServer is stored in tables, while tables are stored in databases.

Sqlserver has five basic databases:

. Master (data dictionary)

. Mode (storage sample)

. Tempdb (temporary database)

. Msdb (storing scheduling and log information)

. Pubs (example database)

Real data is stored together with data dictionaries, and there is no security mechanism for system parameter information.

5. log on management.

Oracle login management:

. System/manager (initial account)

. Sys/change_on_nstall

. Install/oracle (installation account)

. Scott/tiger (Sample Database for testing)

By default, there are only three system users in oracle, and oracle is logged on through the user.

Sqlserver logon management:

. Sqlserver authentication.

. Windows authentication.

In sqlserver, you connect to the database through authentication for a windows User Account or using sqlserver.

6. nature.

Sqlserver is not a language, and it is the same as an oracle database to transmit commands. SQL is a structured query language, and oracle's Structured Query Language is similar to SQL.

7. syntax.

In SQL, null is neither a character nor a number. It is the default value, and oracle provides the nvl function.

The string connection in oracle is string1 | string2, and string1 + string2.

Set Operations: In SQL, only union is supported. oracle contains minus, interect, and union)

Index: SQL indexes include clustered indexes and non-clustered indexes, and full-text indexes.

Oracle indexes include: B + index, bitmap index, function index, reverse index, primary key index, hash index, and local index.

Data Type:

The data types in Oracle are complex, including basic data types, complex type, column object type, and structure type;

The data in SQL is relatively simple. Only some simple basic data types cannot provide transaction operations.

Transaction:

In SQL, if a transaction is wrong from execution to execution, it will roll back to the beginning.

In oracle, it uses point rollback, which is the function of the transaction and precise error locating. It uses savepoint to mark the storage point and rollback to mark the storage point of rollback errors.

Cursor:

In the SQL midstream, the usage of the mark is complicated. You cannot open more than one cursor at the same time because it is only a global variable. @ Fast_statues it is also troublesome to declare the cursor. When the cursor is closed, the memory is not cleared.

The cursor in oracle is a single cursor that increases the speed of unification. It can be read asynchronously and the declaration is relatively simple. You can simply use the declare cursor name is select statement.

Fault Tolerance Mechanism:

The error mechanism in SQL is complicated, and error descriptions are not provided. There are three types of Fault Tolerance in Oracle, one is a predefined error, the other is a non-predefined error, and the other is a user-defined error, in the custom error, two slq values are different, which are the sqlcode error code and sqlerrm error description.

8. Security.

Sqlserver has not obtained any security certificate. Oracle server receives the highest level of ISO certification. DB2 has obtained the highest level of ISO certification.

9. performance.

The performance of Multiple SQL Server users is poor.

Oracle has the highest performance, keeping the world record for TPC-D and TPC-C under windowsNT.

DB2 is suitable for data warehouses and online transaction processing with high performance.

10. Client Support and application mode.

SQL serverCS structure. It only supports windows users. You can use ADO, DAO, LOEDB, and ODBC connections.

Oracle multi-layer network computing supports multiple industrial standards and can be connected by network customers such as ODBC, JDBC, and OCI.

DB2 is a cross-platform, multi-layer structure that supports ODBC, JDBC, and other customers.

11. Complicated and simple operations.

SQL Server is easy to operate, but only has a graphical interface.

Oracle is complex. It provides both GUI and command line, and operates the same way in windowsNT and unix.

DB2 is easy to operate and provides GUI and command lines at the same time. It operates the same way in windowsNT and unix.

12. Use risks.

The Code fully rewritten by sqlserver has been tested for a long time and is constantly delayed. Many features require time to prove that they are not very compatible with early-rising products. Use requires risks.

Oracle is fully backward compatible with its long development experience. It is widely used and has almost no risks.

DB2 giant enterprises are widely used, with good downward compatibility and low risks.

I am familiar with oracle for beginners. I will compare and summarize it with SQL Server later.

SELF: http://www.2cto.com/database/201207/144792.html