Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 26, 2003

The comparison of SQL Server 2000 with Oracle 9i - Page 2

By Alexander Chigrik

Performance comparison

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

TPC tests

The Transaction Processing Performance Council (TPC.Org) is an 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 this article was written, 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 this article was written, 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, 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 advantages in comparison with Oracle 9i Database is that SQL Server is cheaper. Another 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 a 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 a 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
Relational tables,
Temporary tables
Triggers BEFORE triggers,
AFTER triggers,
INSTEAD OF 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
index length 900 749
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 on the experience of the database developers and database administrator than on 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

» See All Articles by Columnist Alexander Chigrik



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM