MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

SQL Server 2000 vs MySQL version 4.1

Alexander Chigrik
chigrik@mssqlcity.com

Introduction
Platform comparison
  • Hardware requirements
  • Software requirements
    • Performance comparison
  • TPC tests
    • Price comparison
    Features comparison
  • T-SQL vs MySQL dialect
  • SQL Server 2000 and MySQL v4.1 limits
    • Conclusion
    Literature

    Introduction

    Often people in newsgroups ask about some comparison of Microsoft SQL Server and MySQL. In this article, I compare SQL Server 2000 with MySQL version 4.1 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, MySQL version 4.1 supports all known platforms, including Windows-based platforms, AIX-based systems, HP-UX systems, 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

    MySQL version 4.1 is not so powerful as SQL Server 2000 and uses less hardware resources. To install MySQL version 4.1, you should have near 32 Mb RAM and near 60 Mb hard disk space. The general MySQL version 4.1 installation does not require additional CPU resources.

    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

    MySQL version 4.1 comes in two editions:

  • Standard
  • Max


    • The Standard edition are recommended for most users and contains general MySQL features. The Max edition includes additional features such as the Berkeley DB storage engine, OpenSSL support, user-defined functions (UDFs), and BIG_TABLE support.

    MySQL version 4.1 requires the following software:

    Platform Operating System Version
    Windows-based Windows 95/98/NT/2000/XP/2003
    Sun Solaris Solaris 8 (SPARC)
    FreeBSD FreeBSD 4.x (x86)
    Mac OS X Mac OS X v10.2
    HP-UX HP-UX 10.20 (RISC 1.0),
    HP-UX 11.11 (PA-RISC 1.1 and 2.0),
    HP-UX 11.11 (PA-RISC 2.0, 64-bit only)
    AIX-Based AIX 5.1 (RS6000),
    AIX 4.3.2 (RS6000),
    AIX 4.3.3 (RS6000)
    QNX QNX 6.2.1 (x86)
    SGI Irix SGI Irix 6.5
    Dec OSF Dec OSF 5.1 (Alpha)

    Performance comparison

    It is very difficult to make the performance comparison between SQL Server 2000 and MySQL version 4.1. 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 second position in the TPC-C by performance results.
    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

    MySQL does not participate in TPC-C tests, they make their own benchmark tests. These tests are not independent, but if you interesting, see this link:
    The MySQL Benchmark Suite

    Price comparison

    SQL Server 2000 is currently available under two licensing options:

  • Processor license.
  • Server/per-seat client access license (CAL).


    • The processor license requires a single license for each CPU in the computer running SQL Server 2000 and includes unlimited client access. You can buy this license when you do not know the number of the clients (for example, if your users will connect to SQL Server 2000 through the internet). This license usually is cheaper than Server/Per-Seat CAL when there are many users connected to SQL Server databases.

    The Server/per-seat client access license (CAL) requires a license for the server and the licenses for each client device. You can use this licensing option when the customers do not need access beyond the firewall and the number of clients is low (for example, 10-20 users for SQL Server 2000 Standard Edition or 30-40 users for SQL Server 2000 Enterprise Edition).

    Licensing Options SQL Server 2000 Standard Edition SQL Server 2000 Enterprise Edition
    Processor $4,999 per processor $19,999 per processor
    Server/Per-Seat CAL with 5 CALs - $1,489
    with 10 CALs - $2,249         		with 25 CALs - $11,099

    The MySQL version 4.1 is Dual Licensed. Users can choose to use the MySQL software as an Open Source/Free Software product under the terms of the GNU General Public License or can purchase a standard commercial license from MySQL AB.
    See MySQL Support and Licensing

    The MySQL v4.1 Server commercial license is per database server (single installed MySQL binary). The price comparisons below were based on the MySQL Licensing Prices from MySQL AB.

    Number of licenses Price per copy (EUR) Price per copy (USD)
    1 .. 9 440.00 495.00
    10 .. 49 315.00 360.00
    50 .. 99 255.00 290.00
    100 .. 249 195.00 220.00
    250 .. 499 155.00 175.00
    500 +
    ask for quote, sales@mysql.com

    Features comparison

    Both SQL Server 2000 and MySQL version 4.1 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 MySQL dialect and show some SQL Server 2000 and MySQL version 4.1 limits.

    T-SQL vs MySQL dialect

    The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by MySQL version 4.1 is called MySQL dialect. Transact-SQL dialect is more powerful language than MySQL dialect. This is the brief comparison of T-SQL and MySQL dialect:

    Feature T-SQL MySQL dialect
    Views General Views,
    Indexed Views,
    Distributed Partitioned Views         		Not Supported
    Triggers AFTER triggers,
    INSTEAD OF triggers         		Not Supported
    Stored Procedures T-SQL statements Not Supported
    User-defined functions Scalar functions,
    Inline table-valued functions,
    Multistatement table-valued functions     		C, C++ external libraries
    Foreign Keys Supported Supported for only InnoDB tables
    Cursors Supported Not Supported
    Arrays Not Supported Supported

    SQL Server 2000 and MySQL v4.1 limits

    Here you can find some SQL Server 2000 and MySQL version 4.1 limits:

    Feature SQL Server 2000 MySQL v4.1
    column name length 128 64
    index name length 128 64
    table name length 128 64
    max indexes per table 250 32
    index length 900 1024
    max index column length 900 255
    columns per index 16 16
    max char() size 8000 1048543
    max varchar() size 8000 1048543
    max blob size 2147483647 1048543
    max number of columns in GROUP BY Limited only by number of bytes (8060) 64
    max number of columns in ORDER BY Limited only by number of bytes (8060) 64
    tables per SELECT statement 256 31
    max columns per table 1024 2599
    max table row length 8036 65534
    longest SQL statement 16777216 1048574
    constant string size in SELECT 16777207 1048565

    Conclusion

    It is not true that SQL Server 2000 is better than MySQL version 4.1 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 MySQL version 4.1 and vice versa.

    The SQL Server 2000 advantages:

    • 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.
    • Transact-SQL is more powerful language than MySQL dialect.
    The MySQL version 4.1 advantages:

    • MySQL version 4.1 supports all known platforms, not only the Windows-based platforms.
    • MySQL version 4.1 requires less hardware resources.
    • You can use MySQL version 4.1 without any payment under the terms of the GNU General Public License.
    This is from MySQL version 4.1 documentation:

    MySQL Server was designed from the start to work with medium size databases (10-100 million rows, or about 100 MB per table) on small computer systems.

    Literature

    1. SQL Server 2000 Books Online

    2. MySQL 4.1 Downloads

    3. Features Available in MySQL 4.1

    4. The MySQL Benchmark Suite

    5. GNU General Public License

    6. MySQL Support and Licensing

    7. MySQL Licensing Prices

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

    9. Top Ten TPC-C by Price/Performance Version 5 Results
     

    		 
    Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

    (c) 1997, 2005 Bits on the Wire, Inc