Here is a brief comparison between Oracle 9i and SQL Server 2000. In my view, Oracle comes out ahead in this c...
SQL Server is only operable on the Windows platform, a major limitation for it to be an enterprise solution. Oracle is available on multiple platforms such as Windows, all flavors of Unix from vendors such as IBM, Sun, Digital, HP, Sequent, etc. and VAX-VMS, as well as MVS. The multi-platform nature of Oracle makes it a true enterprise solution.
Locking and concurrency
SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity. In contrast, with Oracle, the rule is "readers don't block writers and writers don't block readers." This is possible without compromising data integrity because Oracle will dynamically re-create a read-consistent image for a reader of any requested data that has been changed but not yet committed. In other words, the reader will see the data as it was before the writer began changing it (until the writer commits). SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment.
Also, SQL Server will escalate row locks to page level locks when too many rows on a page are locked. This locks rows which are uninvolved in any updates for no good reason.
Performance and tuning
- In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.
- All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.
- No range partioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.
- There is no partitioning in SQL Server.
- There are no bitmap indexes in SQL Server.
- There are no reverse key indexes in SQL Server.
- There are no function-based indexes in SQL Server.
- There is no star query optimization in SQL Server.
Here are some object types missing in SQL Server that exist in Oracle.
- You cannot declare public or private synonyms.
- There is no such thing as independent sequence objects.
- There are no packages; i.e., collections of procedures and functions.
- No "before" event triggers (only "after" event triggers) and no row triggers (only statement).
- No object types like in PL/SQL.
PL/SQL versus T-SQL
- In T-SQL there are significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a code challenge re-write. The INNER JOIN, LEFT OUTER, RIGHT OUTER JOIN syntax differs from the classic JOIN.
- No Java database engine as in Oracle.
- Stored procedures in SQL Server are not compiled until executed (which means overhead, late binding and finding errors at execution time only!).
- No ability to read/write from external files from a stored procedure.
- PL/SQL has many DBMS system packages, where T-SQL relies only on a limited number of extended and system stored procedures in the master database.
- PL/SQL is better in error exception handling, and there is no such thing as exceptions in T-SQL (T-SQL uses @@error -- not elegant!).
- T-SQL does not have the MINUS operator, that makes finding schema differences more difficult in SQL Server.
- In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes.
- In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers.
In clustering technology, Oracle is light years ahead, since SQL Server has nothing like Oracle Parallel Server/RAC -- two instances acting on the same data in active-active configurations. And with the new version of Parallel Server in Oracle 9i, renamed as the Oracle Real Application Clusters, there is diskless contention handling of read-read, read-write, write-read, and write-write contention between the instances. This diskless contention handling is called Cache Fusion, and it means for the first time, any application can be placed in a cluster without any changes, and it scales upwards by just adding another machine to the cluster. Microsoft has nothing like this.
- In SQL Server Standard Edition there is no ability to mirror the transaction log files. In Enterprise Edition there is a log shipping process that is not so reliable.
- If the logs fill up the disk, the database will crash hard.
- Sometimes this requires the server itself to be rebooted.
SQL Server is clearly positioned between Microsoft Access and Oracle in terms of functionality, performance and scalability. It is a good workgroup-level solution, a very quick time to market solution, and is very simple to use and administer. Oracle is much more advanced and has more to offer for larger applications with both OLTP and data warehouse applications. Its new clustering features are ideal for Application Service Providers (ASPs) on the Internet, who can now start with a cluster of two small servers and grow by just adding a server when they need to.
Lucy L. writes: I have worked with both and mostly agree with the author's observations, but I would like to mention the other side of this comment: "No "before" event triggers (only "after" event triggers) and no row triggers (only statement)." In SQL server, the statement-level trigger gives you access to the pseudo-tables INSERTED and DELETED. This means you can perform an action for each row operated on in a setwise fashion rather than in the row-at-a-time fashion you must use in Oracle. This is a much better deal in bulk operations where an Oracle row-level trigger can eat up much of the performance advantage of bulk inserts or updates.
Balaji S. writes: I think it is fundamentally wrong to compare SQL Server with Oracle. I agree with all the points mentioned in the article. Oracle is a more mature and advanced product than SQL Server. Also, I personally faced a problem when we implemented a CRM application over SQL Server. I was told that SQL Server has a limit on the size of the row and that if the row is greater than a certain size, then you need to split the row and insert into two tables with parent child relationship. This happened about a year back and I am not sure whether it is fixed or not. The company tried to migrate their CRM from Oracle to SQL Server but only ended up spending millions to find out that it is not going to work. They are continuing with Oracle now.
Harvinder S. writes: I found your article very informative. Here are some observations:
1) Quote: "Also, SQL Server will escalate row locks to page level locks when too many rows on a page are locked." From my understanding, SQL Server never escalates row level lock to page level lock. It will aquire page/row lock at the beginning and escalate to table level lock if needed.
2) Quote: "In T-SQL there are significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a code challenge re-write. The INNER JOIN, LEFT OUTER, RIGHT OUTER JOIN syntax differs from the classic JOIN." I find these extensions to be very beneficial for writing efficient code.
3) I totally agree that SQL Server should have the following features:
- Indexes - bitmap, function-based, etc
- Better error handling
- Better locking mechanism
I hope the next article will be show SQL Server's advantages over Oracle.
Patrick M. writes: Pure dribble. Clearly the author is an Oracle bigot. For someone that is certified in MS, he sure did get some major items wrong about MS-SQL. Clearly he develops with Oracle and has only played with SQL Server, otherwise he would know better.
- Stored procs are compiled in MS-SQL.
- A feedback from another writer clarifies the before trigger issue (which is my basis for stating he doesn't actually USE MS-SQL).
- Stating that migrating a db from system A to system B is hindered by T-SQL is absurd. That is the least of the issues. If you are moving to another db, you will definitely have huge issues just with the table designs. Lastly, no one puts a gun to your head to use the MS extensions – MS-SQL supports ANSI-92 (this is my basis for stating he is an Oracle bigot – it's a reaching argument to further his bias). Let me take this further, he states in Oracle you can use java and read-write to external files, something you cannot do in MS-SQL, I dare ask, is this ANSI-92 compliant?
- The dead connection detection feature works the same in SQL and Oracle –- they both suck. How do I know, we run both servers in production environments.
- I will grant him that @@error is not elegant like true error handling. I am at a loss to recall if the error handling in PL/SQL is ANSI-92 compliant.
- There ARE packages of procedure in SQL (as for functions, I do not know). This further proves he does not use MS-SQL.
- You can horizontally partition data in MS-SQL.
- Why he defines enterprise as being able to run on multiple platforms is beyond me. Once you have the server to run the software, it's a mute point.
- As for some of the eclectic features he lists for Oracle –- I am curious what of those features he uses today in most of his environments. Just because I can take control over the most minuscule element of Oracle doesn't mean I should. What makes me think I am smarter than the many person-years gone into the server algorithms that keep memory allocation in check?
I probably could go on, but you get the point. This article was a MS-SQL bashing article, not a "differences" article. I find it statistically impossible that MS-SQL doesn't do ANYTHING better than Oracle.
Santosh B. writes: I tend to agree with the reader review of Patrick M. Oracle is a powerful system but SQL is very powerful too and the author has got some things totally wrong. My points are:
1) You can do any sort of writing to and reading from external files , communicate with external apps everything by using external stored procedures. So these are com objects and dlls. but so what ? If you can use java to do this in oracle, you can use ext stored procs in TSQL.
2) My understanding is that LEFT OUTER JOIN etc are ANSI standards! And Oracle has only now started supporting this. It makes coding so much more elegant especially when writing dynamic stored procedures.
3) Has the author not heard of Enterprises that run Microsoft platform? Do they not successfully use SQL Server? Hasn't SQL Server consistently topped all benchmarks?
4) The SQL system is capable of tuning at low levels if you need to and in any case you need more knobs in Oracle since it caters to different platforms. The absence or presence of 100 tunable features doesn't in itself make for a better database.
5) Partitioning is possible in SQL server and there are features like Distributed Partitioning.
6) Sequence objects are not an ANSI standard anyway. And besides, Oracle doesn't have IDENTITY properties for columns.
7) Who needs SQLNETtype connectivity really? We can do the same by using ODBC DSN names and in .NET you use config files. You don't need to change your code.
8) Can he prove that Log Shipping is not reliable? Ok.. so 9i RAC is very powerful. I accept that, but instead of dismissing SQL's solutions, he'd better prove it.
This wasn't a comparison. More like a rant...
Earl C. writes: I absolutely agree that Oracle is superior to SQL Server in the way that a Rolls Royce is superior to my Nissan Sentra. So why am I not driving a Rolls Royce? I guess the answer to that one is that I cannot afford a Rolls Royce and I do not need a Rolls Royce for my trips to Wal-Mart. My point is that for most companies and most databases SQL Server is good enough. I only wish that Oracle offered a much cheaper version of their product with fewer advanced features. By the way, OEM 9i is way better than SQL Server Enterprise Manager but of course you must do some work to set up OEM.
Andy W. writes: I've worked with both SQL Server (yes, the most current stable version) and Oracle 9i. Attempts at comparison is pointless: It's like comparing little league baseball to the major leagues.
There simply is no way to rationalize away the platform dependency issue with SQL Server. Folks that attempt to do so, only emphasize their bias for the MS platform and have little regard for TCO (Total Cost of Ownership) and ROI (Return on Investment) issues that confront IT management, especially in the cost conscious and competitive marketplace we face today.
Don't be overly concerned about the differences in the features, implementation and robustness of the two databases. SQL Server is a young and immature product that eventually will close the gap. What you are investing in is a long-term vendor relationship and you must study very carefully all aspects of that relationship, including licensing, support, standards compliance and portability. Migrating to a new RDBMS is an extremely difficult and expensive process and essentially means you made the wrong choice. Technology exists to serve the business, and careful analysis of current and future business requirements should be the primary consideration when evaluating database technologies.
Don B. writes: Loved the article! I'm glad that this brave DBA has the guts to speak the truth against the Microsoft vaporware.
Bob S. writes: Having worked with MS SQL Server and Oracle and knowing that each have strengths and weaknesses, I'm surprised the author used such flimsy arguments for Oracle and against MS SQL Server. Also, some of the feedback lacks real insight.
Consider the author's claim that Oracle has packages and SQL Server does not. Oracle uses packages with their PL/SQL software. They are wrappers for procedures but have more meaning than just being wrappers as the header and body of the package is designed much the same way an Ada package is created and used. SQL Server does not have this. They use the term differently. Both author and responders are talking past each other.
Personally, I like Oracle better for most DB applications. However, SQL Server is so easy to work with, especially for smaller databases. Backup and recovery is a breeze. Oracle is a Cadillac of RDBMS. For large (100+ GB) databases I cannot imagine trying to perform a recovery on anything less than with an Oracle platform. PL/SQL is a much better language for developing SQL applications. Also, the exception is great. Locking strategy is better for heavy applications. However, Oracle is over-kill for a lot of smaller applications. The real key for most business's is that they need to know there information requirements. If your information requirements are small, don't waste your time on using Oracle. If your information requirements are greater than this and you want the capability of being able to use either Win2k or Unix, stick with Oracle. It's that simple.
All of this talk about who is better than the other or who has what is a lot of wasted air. Both do a good job. As for the author, please research these matters out before making bold statements that are really not completely accurate. Your prejudice really clouds the picture for the non-informed.
Roger P. writes: I agree with Patrick -- this is purely SQL bashing and doesn't appear to be any form of attempt at a real comparison.
Locking and Concurrency - stating that SQL Server's locking architecture is less mature is incorrect. The locking architecture is designed to process requests faster, as is evident by the TPC benchmarks (www.tpc.org), and is different than how Oracle handles it, so what?
"SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment." It's a poor craftsman that blames his tools. I bet I could take that environment and resolve any of those delay/wait issues through code or schema changes or even server configuration. Maintaining locks on objects is part of keeping transactions atomic in any dB environment. Row to page to table escalation reduces resource utilization overhead from trying to manage too many locks. If locks are an issue you should try running more frequent commits. In Oracle when a lock is encoutered the transaction goes through the log looking for the last known good value. The implications of this are a query could certainly return a result set that was not expected if instead it had waited for the current lock/update/insert to complete. It's all a matter of how you view this. There are pros and cons on either side.
Oracle costs more than twice as much and isn't as feature rich for the base cost. For example, out-of-the box SQL Server provides tools to operate in a heterogenous environment. Oracle charges still more for those tools after you pay the base cost. Then they still charge more for maintenance. Microsoft provides all of their updates free of charge.
SQL Server does not require downtime for any type of backups. Despite what the writer says, log shipping is an extremely inexpensive and reliable means of replicating data to a standby server. Scriping a DCL process in the standard edition for log shipping is a simple task as well.
"In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc." Microsoft's goal here is to make these systems more self tuning and less prone to human error. This means that some things are not available to the DBA to change. In the writer's example he also mentions page and extent size, I'd counter that in SQL you don't have to worry about data fragmentation any more as the kernel manages and mantains this. As future releases come out this will become more and more evident. Managing database properites in ASCII files is archaic, subject to error and inconsistencies and is, in plain terms, the wrong way to run things.
"SQL Server is clearly positioned between Microsoft Access and Oracle in terms of functionality, performance and scalability." Puh-lease! It wasn't even until the release of 9i that Oracle started taking a serious look at management features. The only performance advantage Oracle has had was on the 64-bit platform and that advantage is now gone. SQL Server is faster, takes fewer DBAs to support, costs less, offers more robust tools and features that directly contribute to a company's bottom line than Oracle does. Oracle features and it's pricing model contribute only to Oracle's bottom line.
"In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers." Acquaint yourself with NetLib or you could even alias at the DNS server. In any case, at the very least if you're having to hard code server names instead of using a registry or some form of properties file and making this a variable in the app these coding habits leave something to be desired anyway.
"In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes." I've seen the same issues in Oracle, this depends more on the application touching the dB than the specific database vendor.
@@error is a system defined variable to hold the last error and is by no means the only method of trapping and handling exceptions in SQL Server.
Off the soap box for now. :)
David H. writes: I agree with the feedback that asserted this was not a comparison but a SQL Server bashing exercise. I have some experience with PL/SQL and none with SQL Server, and was interested to learn the the relative strengths and weaknesses of the two systems. This articale has not achieved that. It is only half an article.
Seetharaman S. writes: For two days before reading this, I was thinking about exactly the same thing. Surprised to see this today. I have worked and am working on Oracle and SQL databases and I find it difficult to handle SQL databases when the size of the DB goes beyond 4-8 GB. Even indexes are not efficient. We do a monthly process that initially takes 3 hours to complete. Subsequently after running it for two more months, it is taking something around 7 hours to 18 hours. Not joking at all, we tried again and again for the same month before restarting the system. We did and finally it took 3 hours and 39 mins. The DBAs job with respect to SQL Server is bit difficult and at times was not able to answer where and what goes wrong. The other day, I had problem with the SQL server Agent and I found out that it was related to the DTS and NT Fibers option. On another occassion, during installation, using the control panel, I removed the SQL server and restarted more than 10 times. Finally, I realized that some parameter in the registry had a '1' value so that I was not able to perform a fresh installation. Also, while processing cubes, as you said, when we collect huge data, it locks the select statement triggered in some other connectivity. Although SQL Server has great user-friendly screens and installation advancements, it lacks Enterprise Puree, which is nowadays in demand.
Scott T. writes: I agree with Patrick M that this is pure SQL Server bashing and is far too one-sided to be taken seriously. I consider myself as an Oracle DBA and agree with some of the comments, but there are few details about which features are better in SQL Server than Oracle other than the summary provided. To add to Patrick's comments/corrections, you *can* alias SQL Servers via DNS so applications don't need to have the server name "hard coded".
I would like Eli to explain why he considers Oracle better than SQL Server "for both OLTP and data warehouse applications" when SQL Server holds nearly every tpc benchmark, both clustered and non-clustered? M$'s best effort is about 709K vs Oracle's best effort of about 428K, and Oracle is about $3 per transaction more expensive in these configurations!! Also he fails to mention that although Oracle clustering technology is good, it also costs lots of $$$ through its licensing scheme.
Joe Y. writes: Are you sure the author isn't an Oracle employee? I've been an Oracle DBA, SQL DBA, and am now playing with other stuff, including an interesting toy called MySQL. Sure, there are pros and cons to Oracle and SQL, and a few of the points raised here are even valid but really, the "tip" in general looks like a typical Oracle marketing flyer.
The author responds: As a DBA in both Oracle and SQL Server, I tried in this article to find all the things that exist in Oracle but not in SQL Server. Oracle surely has more functionality and is more mature than MS SQL Server but there are some points in favour of SQL Server:
1) Self-tuning, with update statistics checked and auto create statistics.
2) User functions that can operate also as parametric views
3) Insert...Exec stored procedure format that Oracle does not have with the INSERT statement
4) Identity column
5) Very intuitive learning; i.e., it's easier to learn than Oracle.
6) Linked servers are really much easier to implement than external procedures in Oracle.
About the Author
Eli Leiba works at the Israel Electric Company as a senior application DBA in Oracle and Microsoft SQL Server. He has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in computer science and has 11 years of experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and development courses at the Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.