6 Ways SQL Server and Oracle Are Way Different
It's tempting to come to the conclusion that SQL Server and Oracle are essentially interchangeable. After all, a relational database is a relational database, right? But, you'd be wrong. It's not just the label that's different. Looking under the hood, SQL Server and Oracle have evolved into entirely different species.
Oracle designed their database from the outset with enterprise-class performance and reliability in mind. Whereas, Microsoft placed emphasis on easy administration. Oracle has always been laser-focused on raw performance and scalability. Meanwhile, Microsoft built SQL Server for the enterprise but was quick to "Windowize" the software.
Today, both platforms are enterprise-capable and more powerful than ever, yet, traces of a differing philosophy remain. If not well understood, some of these differences could make or break a project.
1. SQL Server is now on Linux, just like Oracle
Oracle and SQL Server have different views about platform support. Oracle is dedicated to supporting as many platforms as possible. Microsoft being Microsoft, ensured SQL Server would only ever run on Windows — until recently.
In 2016, Microsoft announced that SQL Server would run on Linux, and then made good on that promise with Server 2017. New versions of SQL Server can now run on several flavors of Linux, as Azure instances or Docker containers. Because Docker containers allow platform-independent operation of the software within them, the possibilities have really expanded. SQL-Server on a Mac, anyone?
As much as we're fond of SQL Server's newfound openness, Oracle still has a huge advantage when it comes to platform support. By that, we mean Oracle supports more than 60 platforms.
In fact, several operating system flavors have been created specifically to run Oracle as reliably and efficiently as possible, including Oracle Linux. Aside from Linux, Oracle also runs on Windows, VMware, and common enterprise OSes such as Solaris, IBM AIS, and HP-UX. As crazy as it sounds, there are many companies that still operate mainframes. With Oracle, there's no need to retire that mainframe just yet.
2. Oracle uses PL/SQL. SQL Server uses T-SQL.
At first glance, the basic query syntax used by both platforms appears to be nearly identical. All of this changes, as operations become more complex and you dive into procedural programming within the database. Microsoft's T-SQL (Transact-SQL) and Oracle's PL/SQL (Procedural Language for SQL) are very different creatures.
T-SQL's syntax is simpler but sacrifices a lot of performance compared to PL/SQL. The difference between T-SQL and PL/SQL is similar to the difference between developing in Python versus C++. Python is an easy-to-learn high-level language. C++ is a low-level, object-oriented language that's much more robust for creating applications and integrating with hardware.
How PL/SQL implements table clusters really illustrates this point. Using PL/SQL is initially more complex to configure, but it requires far fewer database transactions than it would take with T-SQL. Although T-SQL wins the rapid development prize for large transaction volumes, Oracle takes the decisive lead with PL/SQL.
3. To execute parallel or serial, that is the question
Like any enterprise database, Oracle and SQL Server are limited only by processing power. Given equal processors and cores, Oracle might actually edge SQL Server for one reason — parallel execution. Parallel execution enables transactions to span multiple threads, allowing the operating system to divide workloads among processors and cores.
SQL Server and Oracle both allow serial execution. But most would argue that parallel execution is more robust and efficient in Oracle. That's because Oracle allows parallel processing across more SQL commands with PL/SQL.
Meanwhile, SQL Server uses Query Optimizer to choose whether to run a task in serial or parallel mode, and it often chooses poorly. There's also a deeper issue with T-SQL itself. T-SQL limits some commands to execute in serial mode only.
In instances where SQL Server doesn't perform well out of the box, you can force it. More than anything, this key difference highlights the disparate philosophies of the two products. Oracle is ready and waiting for the biggest task, while SQL Server needs prep and more than a little coaxing.
4. Reading and Writing Concurrently
Concurrency is one of the main advantages of using a database rather than a flat file like a spreadsheet. Concurrency means that databases can be updated by many users at the same time. For large companies, that can mean thousands. As users are reading and writing to a database, the servers sort out all the changes to maintain integrity. But SQL Server and Oracle handle these concurrent processes much differently.
SQL Server will hold new reads until a writer completes, while Oracle will allow reads during that time. In sum, Oracle readers do not block writers, and writers don't block readers. For that reason, Oracle runs quicker than SQL Server.
5. BEFORE triggers and AFTER
For two seemingly comparable products, Oracle and SQL Server manage to implement triggers in very different ways. The Microsoft approach is to only allow AFTER triggers, while Oracle also implements BEFORE triggers.
For instance, when performing a banking debit transaction, a wise developer will ensure that sufficient funds are available using a BEFORE trigger. In situations where BEFORE triggers are important, Oracle avoids the annoying workarounds that would be necessary with SQL Server.
Microsoft's version has less potential for Kills, but greatly reduces the usefulness of triggers.
6. Pricing between Oracle and SQL Server is just a little different
You could be forgiven for believing that Oracle is more expensive than SQL Server. It was once that way. However, as part of Oracle's embrace of the open source community, some versions of Oracle are actually free, both for development purposes and, in certain cases, commercial use. Oracle XE (Express Edition) can be used freely for low demand database applications. Microsoft has one-upped Oracle with a free SQL Server Express version in a broader plan to gain market share.
In keeping up with the competition, standard editions of both databases tend to be priced about the same. The big difference here lies with their enterprise versions. Oracle Enterprise Edition still commands the highest licensing costs for its efficiency and massive scalability.
Two Different Species in the Same Space
Microsoft and Oracle databases have evolved into two different species in the past 30 years. SQL Server serves the broad market of businesses that already employ Microsoft services and infrastructure. They're slowly widening their reach with products like SQL Server on Linux, and attracting more clients with Azure.
Oracle is still the favorite among corporations and governments. It's not as easy to use as the point-and-click SQL Server, but simple isn't always better. For large organizations relying on complex data structures and high transaction volumes, configuration power over simplicity wins — and that's Oracle.
delivered to your inbox.