Monday, January 12, 2009

SQL Server 2000 vs Oracle 9i


Reference:- http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
Alexander Chigrik
chigrik@mssqlcity.com


Introduction
Platform comparison

  • Hardware requirements



  • Software requirements


    • Performance comparison

  • TPC tests


    • Price comparison
    Features comparison

  • T-SQL vs PL/SQL



  • SQL Server 2000 and Oracle 9i limits


    • Conclusion
    Literature

    Introduction

    Often people in newsgroups ask about some comparison of Oracle and Microsoft SQL Server. In this article, I compare SQL Server 2000 with Oracle 9i Database regarding price, performance, platforms supported, SQL dialects and products limits.

    Platform comparison

    SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.
    In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

    Hardware requirements

    To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware:

    Hardware Requirements
    Processor Pentium 166 MHz or higher
    Memory 32 MB RAM (minimum for Desktop Engine),
    64 MB RAM (minimum for all other editions),
    128 MB RAM or more recommended
    Hard disk space 270 MB (full installation),
    250 MB (typical),
    95 MB (minimum),
    Desktop Engine: 44 MB
    Analysis Services: 50 MB minimum and 130 MB typical
    English Query: 80 MB

    Oracle 9i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

    To install Oracle 9i under the Intel or compatible platforms, you should have the following hardware:

    Hardware Requirements
    Processor Pentium 166 MHz or higher
    Memory RAM: 128 MB (256 MB recommended)
    Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB
    Hard disk space 140 MB on the System Drive
    plus 4.5 GB for the Oracle Home Drive (FAT)
    or 2.8 GB for the Oracle Home Drive (NTFS)

    To install Oracle 9i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware:

    Hardware Requirements
    Memory A minimum of 512 MB RAM
    Swap Space A minimum of 2 x RAM or 400 MB, whichever is greater
    Hard disk space 4.5 GB

    Software requirements

    SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE) and requires the following software:

    Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE
    Windows CE No No No No No Yes
    Windows 9x No No Yes No Yes No
    Windows NT 4.0 Workstation with Service Pack 5 No No Yes Yes Yes No
    Windows NT 4.0 Server with Service Pack 5 Yes Yes Yes Yes Yes No
    Windows NT 4.0 Server Enterprise Edition with Service Pack 5 Yes Yes Yes Yes Yes No
    Windows 2000 Professional No No Yes Yes Yes No
    Windows 2000 Server Yes Yes Yes Yes Yes No
    Windows 2000 Advanced Server Yes Yes Yes Yes Yes No
    Windows 2000 DataCenter Yes Yes Yes Yes Yes No
    Windows XP Professional No No Yes Yes Yes No

    Oracle 9i Database comes in three editions: Enterprise, Standard and Personal and requires the following software:

    Platform Operating System Version Required Patches
    Windows-based Windows NT 4.0 Service Pack 5
    Windows-based Windows 2000 Service Pack 1
    Windows-based Windows XP Not Necessary
    AIX-Based AIX 4.3.3 Maintenance Level 09 and IY24568,
    IY25282, IY27614, IY30151
    AIX-Based AIX 5.1 AIX 5L release 5.1 ML01+ (IY22854),
    IY26778, IY28766, IY28949, IY29965, IY30150
    Compaq Tru64 UNIX Tru64 5.1 5.1 patchkit 4
    Compaq Tru64 UNIX Tru64 5.1A 5.1A patchkit 1
    HP-UX HP-UX version 11.0 (64-bit) Sept. 2001 Quality Pack, PHCO_23792,
    PHCO_24148, PHKL_24268, PHKL_24729,
    PHKL_ 25475, PHKL_25525, PHNE_24715,
    PHSS_23670, PHSS_24301, PHSS_24303,
    PHSS_24627, PHSS_22868
    Linux SuSE Linux Enterprise Server 7
    (or SLES-7) with kernel 2.4.7,
    and glibc 2.2.2         		Not Necessary
    Sun Solaris Solaris 32-Bit 2.6 (5.6), 7 (5.7)
    or 8 (5.8)         		Not Necessary
    Sun Solaris Solaris 64-Bit 8 (5.8) Update 5

    Performance comparison

    It is very difficult to make the performance comparison between SQL Server 2000 and Oracle 9i Database. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms.

    TPC tests

    The Transaction Processing Performance Council (TPC.Org) is independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy.

    The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.

    The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the     TPC-C test (OLTP test).

    At the moment the article was wrote, SQL Server 2000 held the top TPC-C by performance results with Distributed Partitioned Views-based cluster systems.
    See     Top Ten TPC-C by Performance Version 5 Results

    At the moment the article was wrote, SQL Server 2000 held the top TPC-C by price/performance results. See     Top Ten TPC-C by Price/Performance Version 5 Results

    Note. Because most organizations really do not run very large databases, so the key points on which SQL Server 2000 won the TPC-C benchmarks do not really matter to the vast majority of companies.

    Price comparison

    One of the main Microsoft SQL Server 2000 advantage in comparison with Oracle 9i Database is that SQL Server is cheaper. Other SQL Server advantage is that Microsoft includes the Online analytical processing (OLAP) and Data Mining as standard features in SQL Server 2000 Enterprise Edition. So, you can save up to four times with SQL Server 2000 Enterprise Edition if you use OLAP and Data Mining.

    The price comparisons below were based on the
    Oracle and SQL Server 2000 Price Comparison
    article from Microsoft.

    Compare pricing for SQL Server 2000 Standard Edition and Oracle9i Standard Edition:

    Number of CPUs Oracle9i Standard Edition SQL Server 2000 Standard Edition
    1 $15,000 $4,999
    2 $30,000 $9,998
    4 $60,000 $19,996
    8 $120,000 $39,992
    16 $240,000 $79,984
    32 $480,000 $159,968

    Compare pricing for SQL Server 2000 Enterprise Edition (which include OLAP and Data Mining) and Oracle9i Enterprise Edition with OLAP and/or Data Mining:

    Number of CPUs Oracle9i Enterprise Edition Oracle9i Enterprise Edition with OLAP or Data Mining Oracle9i Enterprise Edition With OLAP and Data Mining SQL Server 2000 Enterprise Edition
    1 $40,000 $60,000 $80,000 $19,999
    2 $80,000 $120,000 $160,000 $39,998
    4 $160,000 $240,000 $320,000 $79,996
    8 $320,000 $480,000 $640,000 $159,992
    16 $640,000 $960,000 $1,280,000 $319,984
    32 $1,280,000 $1,920,000 $2,560,000 $639,968

    Note. This is not a full price comparison between SQL Server 2000 and Oracle 9i Database. It is only a brief comparison. You can have any discounts and the prices can be increased or decreased in the future. See Microsoft and Oracle to get more information about the price of their products.

    Features comparison

    Both SQL Server 2000 and Oracle 9i Database support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article I want to make the brief comparison of the Transact-SQL with PL/SQL and show some SQL Server 2000 and Oracle 9i Database limits.

    T-SQL vs PL/SQL

    The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by Oracle 9i Database is called PL/SQL. PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL:

    Feature PL/SQL T-SQL
    Indexes B-Tree indexes,
    Bitmap indexes,
    Partitioned indexes,
    Function-based indexes,
    Domain indexes         		B-Tree indexes
    Tables Relational tables,
    Object tables,
    Temporary tables,
    Partitioned tables,
    External tables,
    Index organized tables         		Relational tables,
    Temporary tables
    Triggers BEFORE triggers,
    AFTER triggers,
    INSTEAD OF triggers,
    Database Event triggers         		AFTER triggers,
    INSTEAD OF triggers
    Procedures PL/SQL statements,
    Java methods,
    third-generation language
    (3GL) routines         		T-SQL statements
    Arrays Supported Not Supported

    SQL Server 2000 and Oracle 9i limits

    Here you can find some SQL Server 2000 and Oracle 9i Database limits:

    Feature SQL Server 2000 Oracle 9i Database
    database name length 128 8
    column name length 128 30
    index name length 128 30
    table name length 128 30
    view name length 128 30
    stored procedure name length 128 30
    max columns per index 16 32
    max char() size 8000 2000
    max varchar() size 8000 4000
    max columns per table 1024 1000
    max table row length 8036 255000
    max query size 16777216 16777216
    recursive subqueries 40 64
    constant string size in SELECT 16777207 4000
    constant string size in WHERE 8000 4000

    Conclusion

    It is not true that SQL Server 2000 is better than Oracle 9i or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider. But SQL Server 2000 has some advantages in comparison with Oracle 9i and vice versa.

    The SQL Server 2000 advantages:

    • SQL Server 2000 is cheaper to buy than Oracle 9i Database.
    • SQL Server 2000 holds the top TPC-C performance and price/performance results.
    • SQL Server 2000 is generally accepted as easier to install, use and manage.
    The Oracle 9i Database advantages:

    • Oracle 9i Database supports all known platforms, not only the Windows-based platforms.
    • PL/SQL is more powerful language than T-SQL.
    • More fine-tuning to the configuration can be done via start-up parameters.

    Literature

    1. SQL Server 2000 Books Online

    2.     Oracle documentation

    3.     Oracle and SQL Server 2000 Price Comparison

    4.     Why is SQL Server better/worse than Oracle?

    5.     Top Ten TPC-C by Performance Version 5 Results

    6.     Top Ten TPC-C by Price/Performance Version 5 Results
    Posted by at
    Labels: