Differences between Oracle and SQLserver

Difference between Oracle and SQLserver 1. oracle supports multiple operating systems, and SQL server only supports windows. 2. oracle gives dba more flexibility. You can adjust parameters based on actual conditions to optimize the performance of your application. Therefore, it is difficult to get started with a disadvantage. SQL server is the opposite, of course, it is easy to get started. Architecture: oracle file architecture: data files. dbf (real data) log file. rdo control file. ctl parameter file. the file architecture of orasql server is :. mdf (data dictionary ). ndf (data file ). ldf (Log File) www.2cto.com oracle Storage Structure: There are two block parameters in oracle: pctfree (fill factor) and pctused (reuse factor), you can control the block to determine when the block itself has, when there is not enough space to accept new information (the analysis mechanism for Block Storage) can reduce the possibility of data row connection and row migration. The block size can be set (oltp block and dss block) in oracle, the continuous Block Composition area, A dynamically allocable partition can reduce the number of space allocations. In oraclel, the table can be divided into multiple segments, which are composed of multiple partitions, each segment can be specified in which table space (the segment type is divided into data segment, index segment, rollback segment, temporary segment, and 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 basic tablespaces: · system tablespaces (which store the information required by data dictionaries and Data Management) · rbs rollback tablespace · temp temporary tablespace · tools interactive tablespace · users user default tablespace · indx index tablespace · dbsys Fu data table space different data are placed in different tablespaces (data dictionary and real separate data storage ), in oracle, base tables (stored system parameter information) are encrypted and cannot be accessed by anyone. It can only be viewed in the user's Visual View. The SQL server storage structure uses the page as the minimum allocation unit, and each page is 8 KB (uncontrollable, lack of analysis mechanism for page storage ), you can combine eight consecutive pages into a 'extension' to further reduce the resources consumed during allocation. (Allocation lacks flexibility). Data is stored in SQL server as tables, while tables are stored in databases. SQL server has five basic databases: www.2cto.com · master (data dictionary) · mode (storage sample) · tempdb (temporary database) · msdb (storage scheduling information and log information) · pubs stores the real data and data dictionary together. There is no security mechanism for system parameter information. Oracle logon 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 logs in through the user. SQL server login management: · SQL server Authentication · windows Authentication in SQL server is connected to the database through a windows User Account or SQL server Authentication. SQL is not a language, but a method for Transmitting commands to oracle databases. In SQL, null is neither a character nor a number. It is the default data. Oracle provides nvl functions. In oracle, the string connection is string1' string2, and in SQL is string1 + string2. set operation: Only union (Parallel Operation) is performed in SQL, and minus (differential operation) is included in oracle) interect and union ). Index: SQL indexes include clustered indexes and non-clustered indexes, and full-text indexes. oracle indexes include B + tree indexes, bitmap indexes, function indexes, reverse indexes, and primary key indexes, hash index, local index. The Data Types of www.2cto.com oracle are complex, including basic data types, complex type, column object type, table object type, and structure type. The data in SQL is relatively simple, transaction operations cannot be provided only for some simple basic data types. In SQL, if an error occurs during the execution of a transaction from the beginning to the end, it will roll back to the beginning. In oracle, it uses a point rollback, this is the transaction function and precise error locating. The savepoint is used to mark the save point, and the rollback is used to mark the save point of the rollback error. In SQL, the loop is only the while type, which is not flexible. in oracle, there are multiple loops (loop, while loop, for loop ). The usage of a cursor in the SQL midstream is complicated. You cannot open more than one cursor at the same time, because it only has one global variable @ fast_statues and it is troublesome to declare the cursor. When the cursor is closed, the memory is not cleared; in oracle, the cursor is a single cursor that increases the speed of unification. It can be read asynchronously by multiple users, and the statement is relatively simple. You can simply use the declare cursor name is select statement. Fault Tolerance Mechanism: The error mechanism in SQL is complex and does not provide Error Descriptions. There are three types of Fault Tolerance in oracle: predefined errors, non-predefined errors, and custom errors, two of the custom errors are not in SQL, that is, sqlcode error code and sqlerrm error description. File architecture: SQL has. mdf (main data file ),. ndf (Extended Files, can have multiple ),. ldf (multiple log files can be stored in online redo log data. One disadvantage of the log files here is that if the log files are small and exquisite, SQL automatically stops running and waits for manual intervention. Therefore, you must monitor the log to ensure the stable operation of the system ). Oracle has. dbf (main data file ),. rdo (log files used for catastrophic data backup ),. ctl (control file: maps physical database files to logical tablespace in the data dictionary and redo log files of the connected machine to ensure data consistency ),. ora (parameter file ). SQL can only be used to back up databases on the local machine. It cannot be used for online backup, and the backup compression is very low, occupying a large amount of space. oracle provides 7 backup mechanisms with the online backup function, A logging backup machine. By default, there are three oracle logs. First, logs 1 are written. When logs 2 are full, logs 3 are written, when log 3 is full, oracle automatically reserves the content of log 1, and then clears it and writes New log information. oracle logs have the multiplexing function, we can create multiple images for logs and write the same log information to multiple log files at the same time to prevent data loss caused by disk damage. SQL server can allocate only one log file to each database. When the log is filled up, the log stops working and waits for manual intervention, but cannot be reused automatically. The control file of www.2cto.com oracle records the physical location and validity of the database file. Each time the oracle system is opened, the data is automatically verified to check its validity and prevent data loss, this reflects oracle's strict data management. SQL server does not have this security mechanism. The system only checks whether data exists after the database information is used. The parameter file init. ora is adjustable. We can adjust the usage of oracle system resources based on the database scale to achieve the most reasonable resource allocation and achieve the best performance for oracle. The configuration parameters of SQL server are internal and cannot be adjusted. They are fixed for system resource allocation and are not under the control of support. Therefore, they cannot simultaneously handle the needs of a large number of users, this limits that it can only be used as a medium or small database. Oracle uses blocks as the minimum storage unit and partitions as the unit for space allocation. You can define the block size as needed, and zones can be allocated in equal or ascending order, this greatly reduces system I/o operations and improves database performance. In SQL server, page-based or extended types are used to allocate space on 8 pages, and manual intervention is not allowed. In case of frequent storage or massive data storage, the system will perform frequent I/o operations to make work less efficient. The SQL statements in oracle are standardized and provide complete functions and data types. Oracle provides a sound error mechanism that allows you to easily obtain and modify the error location and cause. You can also define the required error types to check logical errors. Data type conversion and cursor definition in oracle are also very convenient. Therefore, it is much easier to write and maintain code than SQL server. SQL server uses C-like languages, making it difficult to maintain and view code. SQL server contains too few data types and cannot define struct, arrays, and objects. SQL server cannot define the process mechanism, and type conversion is extremely inconvenient. SQL server has complicated definitions of cursors. When multiple cursors are opened at the same time, the system only has one cursor variable, this is a serious defect in SQL server. In oracle, you can define the fill factor (percentage of free space) and reuse factor (when the storage space of the block falls below this percentage) when creating the block based on data requirements, block to be remarked as available ). You can set the appropriate fill factor and idle factor based on the record change frequency and data size. SQL server only has a fill factor, and its definition is just opposite to that of oracle. It defines the percentage of available space. SQL server does not reuse factors. When the data on the page is lower than the fill factor, the remaining space is no longer possible to insert new data, but the page is still marked as available, it is a waste of time to access this page when the system writes new data. This page is called a waste page, which will waste a lot of time searching for available pages when the system inserts new data. When creating a table, oracle allows you to precisely define the tablespace where data is stored, or even store a table separately in multiple tablespaces. This allows you to store tables with a large amount of data separately by certain fields, this results in extremely high query efficiency. Www.2cto.com with the rapid development of information technology, data processing not only requires more and more data, but also requires higher quality. The stability of the operating system is critical to the database. On the database operating platform, oracle can run on all mainstream platforms. oracle databases adopt open policy objectives, it allows customers to select a solution that best suits their specific needs. Customers can use a variety of third-party applications and tools. It is a great support for developers. However, SQL server can only run on windows, Which is monotonous. However, the performance of SQL server on the windows platform, the overall integration with the windows operating system, is convenient to use, the integration with microsoft development platform is much better than that of oracle. However, the stability and reliability of the windows operating system are obvious to all. Besides, microsoft's policy objective is to lock customers into the windows platform environment. Only with the improvement of windows performance, SQL server can be further improved. From the operating platform, oracle is superior to SQL server. From the information, we can see that oracle Security Certification has obtained the highest level of iso certification, while SQL server has not received any security certification. This proves that the security of oracle is higher than that of SQL server.
When purchasing a product, the first consideration is of course the performance of the product. When considering the performance, of course, the cost of the product is also a key issue. Create and run a database system. Not only does it cover the cost of the initial purchase of software and hardware, but also the cost of training and subsequent maintenance. The price of the orcale database is far higher than that of the SQL server database. Why is the price of the oracle database much higher than that of the SQL server database? On the one hand, the initial cost of oracle is relatively high, especially when considering the tool software, many oracle tool software needs to be purchased separately. Compared with the Free SQL server tool software provided by microsoft, oracle is more expensive. However, because SQL server can only run in windows, it is generally considered that the reliability of SQL server databases is relatively poor. The performance advantage of oracle is reflected in its multiple users, while the performance advantage of SQL server is insufficient for multiple users. In terms of operation, SQL server is much simpler than orcale. If you have used java and dotnet development platforms, the difference is that oracle and SQL server are different, and the oracle interface is basically based on java, most of the tools are dos interfaces and even sqlplus. SQL server is similar to vb and has a full graphic interface. dos windows are rarely seen. The Enterprise Manager in SQL server provides you with an integrated management console with a full graphical interface to centrally manage multiple servers. Oracle also has its own enterprise manager, and its performance exceeds the Enterprise Manager of SQL server in some respects, but it is difficult to install it. Which of the orcale databases and SQL server databases is faster? It is actually a difficult question to answer, because there are many uncertainties, including the processing type, data distribution, and hardware infrastructure. SQL server 2000 is a database product with full web support. It provides the core support of Extensible Markup Language and the ability to query on the internet and outside the firewall. It is scalable, flexible, and scalable, and the price is cheap, and oracle is reliable and secure, which is hundreds of times faster than SQL server, but its price is several hundred times higher than SQL server in implementation. In fact, it is determined by the characteristics of databases and the comprehensive consideration of their own business needs and infrastructure. What are the differences between www.2cto.com ORACLE and SQLSERVER? Which is better? What are their strengths ?? ----------------------------------------------------------- The former is a large database, the latter is a redundant UNIX database, and the excess win server is not an order of magnitude with explain SQL server and oracle. Do not compare it. It's better to learn it. There are also many old iron people talking about this --------------------------------------------------------- openness: SQL Server can only run on windows, without any openness. The stability of the operating system is very important to the database. The Windows9X series products focus on desktop applications, and the NT server is only suitable for small and medium enterprises. In addition, the reliability, security, and scalability of the windows platform are very limited. It is not as tested as unix, especially when dealing with key businesses with large data volumes. Oracle can run on all major platforms (including windows ). Fully supports all industrial standards. Adopt a fully open policy. Customers can select the most suitable solution. Provide full support to developers. DB2 can run on all major platforms (including windows ). It is most suitable for massive data volumes. DB2 is the most widely used in enterprise-level applications. Of the 500 largest enterprises in the world, almost 85% of them use DB2 database servers, and about 5% in China to 97 years. scalability: the parallel SQL server DB2 parallel implementation and coexistence model is not mature. It is difficult to process the increasing number of users and data volumes. Limited scalability. The Oracle Parallel server enables a group of nodes to share the work of the same cluster to expand the capabilities of the mongonets, providing high availability and high scalability cluster solutions. If windowsNT cannot meet your needs, you can move the database to UNIX. DB2 DB2 has good concurrency. DB2 expands database management to a parallel, multi-node environment. Database partitions are a part of the database, including its own data, indexes, configuration files, and transaction logs. Database partition is sometimes called node or database node security. SQL server does not receive any security certificate. Oracle Server receives the highest level of ISO certification. DB2 has obtained the highest level of ISO certification. Performance SQL Server multi-user poor performance Oracle performance is the highest, 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. The client support of www.2cto.com and the SQL Server C/S structure of the application mode. It only supports windows customers and can be connected using ADO, DAO, OLEDB, and ODBC. oracle multi-layer network computing supports a variety of industrial standards. Network customers such as ODBC, JDBC, and OCI can be used to connect to DB2 cross-platform and multi-layer structures. It supports ODBC, JDBC, and other customers to perform simple SQL Server operations, however, there is only a graphical interface. oracle is complicated. It provides both GUI and command line. It is easy to operate the same DB2 in windowsNT and unix, and provides GUI and command line, the same operation in windowsNT and unix uses the code completely rewritten by SQL server, which has undergone long-term tests and is constantly delayed. Many functions require time to prove. Not very compatible with earlier products. Use requires a certain risk. Oracle is fully backward compatible with its long development experience. It is widely used. There is no risk at all. DB2 is widely used in giant enterprises and has good downward compatibility. Low risk. Author: AutomaticThoughts