A brief comparison between Oracle and SQL Server. In my view, Oracle comes out ahead in this comparison.
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.
Source : http://searchoracle.techtarget.com/tip/Oracle-vs-SQL-Server-Why-Oracle-wins
What version of SQL Server are you talking about?
– There is no partitioning in SQL Server
– T-SQL uses @@error — not elegant!
Shame on you. A 2011 write up that tries to compare the Old Microsoft SQL Server 2000 with the a newer Oracle 11g. I hate to disappoint you but you are very obsolete on many accounts.
As for “Locking and concurrency”. SQL Server does have RCSI – Read-Committed Snapshot Isolation, which creates a copy of the modified record in tempdb, performs all the neccessary DMLs and commits it to the “native” datafile, using Oracle notation. It kind of emulates row-versionning and all allows concurrent view and modification of data. The only two concern are: have enough datafiles assigned to tempdb and switch back to the default (read-committed) mode before rebuilding/defragging indexes. Works like a charm! Also, locking mechanism have been drastically improved since SQL 2000, which judging by the rest of your article context, you refer to.
Also, there are partitions past SQL Server 2005, including range ones. And you can swap and merge them exactly alike you do in Oracle. Yes, partitioning operations in SQL Server are kind of cumbersome, the code and approach (when you have to have so called partition scheme and partition function before actually splitting a table into multiple partitioning) is kind of… “funny” if you’re used to Oracle’s partitioning but it does work and works well enough.
Also, SQL Server has indexed views as its counterpart to Oracle’s functional indexes, sequences, exactly like they are in Oracle, have been introduce in SQL Server 2012 and the rest of the “comparison points” you list are either rather not accurate or completely incorrect (such as SQL Server does have a dead connection detection mechanism, – of course!) or superficial or hardly of any importance or, at last, are just adding a bit more comfort (such as PL/SQL datatypes) for developers but can be easily offset by existence of some T-SQL features which Oracle either doesn’t have or more complicated to deal with in Oracle (temporary tables, table variables, “select into”, etc.)
As far as Reliability is concerned, your comments are a complete mess. First, you’re totally mixing up concepts of SQL Server Mirroring and Log Shipping, in addition to their support in Standard vs Enterprise SQL Server versions. Second, no, nothing crashes and nothing requires restart if logs fetch up the disk – it’s like me saying “SID crashes if ran in Achive Log Mode archive log files fill up the disk”… So not true, isn’t it?
I do agree on your “Oracle is years ahead in Clustering Technology” – that’s true, but only when it comes to RAC and so far this is about the only accurate statement you made in your entire comparison of Oracle to SQL Server.. Yes, SQL Server doesn’t have anything like Oracle RAC, but still has some wicked High-Availability solutions, which are more than sufficient for the total majority of HA-scenarios.
You might find it very useful for yorself to go over this post for an accurate comparison analysis of Oracle vs SQL Server:
as it seems like you have some very profound misconceptions regarding modern SQL Server versions (past SQL2K, which probably was the last one you’re familiar with as it does seem like)