Before Christmas I was asked to compare SQL Server against Oracle. So I thought that would make a good SQL Server versus Oracle post. Although I have thought about this over years as I have worked with clients that have had both SQL Server and Oracle, when you sit down to write down and convey the differences; it turns out this is not an easy question to answer. Especially in 3-5 bullets points or a short blog post.
Whichever approach you take, someone who is an avid oracle fan/user/DBA will find a contrary argument and the same for a SQL Server fan/user/DBA; who will find an opposite viewpoint and will argue to the contrary.
These products have many similarities in the functionality that they offer. They are after all, both relational database management systems (RDBMS); but they have a great many differences too.
Let’s start with the similarities. Both are successful and popular database engines, supporting mission critical applications and software across the globe. Both SQL Server and Oracle are relational databases management systems, that will provide a robust and reliable data platform; for your business-critical applications and decision support systems.
They have many similar features:
SQL Server and Oracle have a few differing features and functionality.
We mentioned above that both SQL Server and Oracle have an implementation of Structured Query Language (SQL). SQL Server and Oracle have similar but not identical implementations of ANSI/ISO Structured Query Language (SQL). One example of the difference is string concatenation. In SQL Server’s Transact-SQL (T-SQL) the + is concatenation operator.
SELECT 'String1' + ' ' +'String2'
In oracle you would use a double pipe ||
SELECT 'String1' || ' ' ||'String2'
The examples above concatenate string1 to a literal space and the result of that to string2.
Transaction Control – by default SQL Server has implicit transactions. You can have explicit transactions in SQL Server, but the default behavior is implicit transactions. In Oracle nothing is committed until someone explicitly commits the transaction.
The following illustrates how SQL Server implicit transactions work. If the following code is run the change to the column firtanme for empid 1 is committed automatically.
Update Emps SET FirstName = 'Seth' Where EmpID = 1
In oracle the transaction will remain open until a COMMIT is issued.
Update Emps SET FirstName = 'Seth' Where EmpID = 1 COMMIT
To have an explicit transaction in SQL Server, you have to start the transaction using a BEGIN TRANSACTION command and then COMMIT or ROLLBACK when the transaction is complete.
BEGIN TRANSACTION Update Emps SET FirstName = 'Seth' Where EmpID = 1 COMMIT
Different products mean different tools. In SQL Server you have a graphical user interface (GUI) called SQL Server Management Studio. You also have command line tool called SQLCMD and CMDELTS in PowerShell. In Oracle you have SQL Developer which is also a GUI tool and SQLPlus which is the command line tool. The screen shot below shows SQL Server Management Studio. With its object explorer which can be useful for working with database objects, tables, indexes, column names etc. It also has a query window for writing SQL statements and executing them. This is tool for both SQL Server administrators and developers.
SQL Server doesn’t run on Linux… Well not just yet anyway. At least not in production environments. Until the latest version of SQL Server which is SQL Server 2016. SQL Server only runs on the Windows operating systems. Going forward with the next release of SQL Server. Microsoft are intending to allow SQL Server to run on both windows and different flavors of Linux operating systems. The biggest change to the SQL Server platform that there has been for some time. Oracle runs on Windows Unix and all flavors on Linux and has done for as long as I can remember. So when it comes to multiple operating system support oracle is way ahead of SQL Server.
Writing the above has helped me realize that there is in fact not a great deal of difference between SQL Server and Oracle these days, in terms of the functionality that they offer. They may use different terminology to describe certain pieces of functionality. Over the years as one provider has introduced a new feature the other provider has implemented a similar set of functionality into their product. The features have different names in each product, so it’s not always easy to see that same functionality exists; but generally it’s possible to achieve the same functionality on each database platform.