SQL Server vs. Oracle Database: What You Need to Know

The world of big data is expanding by the second. Businesses large and small are struggling to manage the information under their command. This data includes sales figures, marketing campaign statistics, customer preferences and behavior, and more. Hidden away in this data are many potentially valuable insights that can help your company and your employees perform better.

According to a 2016 survey by HubSpot, the average organization now oversees 163 terabytes of information. That’s enough space to store 40,000 feature-length movies on DVD.

For companies that grow and scale, finding the best way to efficiently handle this information is challenging. Most large enterprises have traditionally stored their data in a relational database management system (RDBMS).

Microsoft SQL Server and Oracle Database are two of the most popular, time-tested options for relational database management in large enterprises. Because they’re both very strong alternatives, making the choice between SQL Server and Oracle isn’t always easy. You’ll need an in-depth analysis of each one’s capabilities, as well as your goals and needs as an organization.

In this article, we’ll go over everything you need to know when comparing Oracle vs. SQL Server. We’ll start with an overview of relational databases, and then discuss the facts about both alternatives and the pros and cons of using them.

What is an RDBMS?

Different types of databases have their own methods of organizing and connecting information. A relational database management system (RDBMS) uses the concept of relational databases to manage data..

Relational databases are databases that organize information in tables with columns and rows. Big data experts refer to this approach as the “relational model.”

Familiar with spreadsheet applications such as Microsoft Excel? You already have a good idea of what the relational model looks like. Excel lacks the performance and sophistication of a true relational database. Yet like a relational database, Excel uses rows and columns to store information in tables.

Example: University Student Database

Let’s say that you want to store data about the students at a particular university. This data includes their name and contact information, as well as the classes that they’re taking.

There are multiple ways to organize information under the relational model. For example, one table could hold the students’ personal information. You could also have one table for each class offered at the university.

The “Students” table will have columns such as:

• first_name
• last_name
• birthdate
• address
• phone_number
• student_ID

Each column can only contain a specified data type, such as a string, integer, or date. Each row of the table contains the information of a different student.

Let’s also suppose that we have a “Geology101” table. This table contains information about all students who are taking the Geology 101 class.

Note that students can have the same first and last name, but not the same ID number. This means that we should use the “student_ID” field to uniquely identify students, not their names.

Besides the “student_ID” column, the “Geology101” table might also have columns for the student’s exam results, homework scores, and final grade.

SQL

Nearly all relational database management systems use SQL (Structured Query Language), a domain-specific language for storing, accessing, and manipulating information stored in relational databases.

SQL queries are commands that obtain information from a relational database. In general, basic SQL queries use three clauses: SELECT, FROM, and WHERE.

• SELECT specifies the columns that you want to receive in the results.
• FROM specifies the table that you want to query.
• WHERE specifies the conditions that each entry in the table must meet to appear in the results.

Suppose that you want the student IDs of all students enrolled in Geology 101 who received a grade below C on the first midterm. The corresponding SQL query would look like:

SELECT student_ID

FROM Geology101

WHERE midterm1 < 70;

This query searches the “midterm1” column in the “Geology101” database to find entries that are less than 70 (i.e., a grade below C). It then returns the corresponding entry in the same row under the “student_ID” column.

If you instead want all information about all students enrolled at the university, you could use the simple SQL query:

SELECT *

FROM Students;

The asterisk * keyword selects all fields in the database. Note that the WHERE clause is optional in this example.

Relational vs. Non-relational Databases

Relational databases are the most common paradigm for organizing large amounts of information. However, they are by no means the only one.

“Non-relational databases” (also called NoSQL databases) organize data in a manner other than the traditional rows and columns of a relational database. They may take the form of keys and values, graphs, documents, or several other paradigms.

Relational databases are typically only capable of working with structured data. This is information that has been neatly organized into discrete standardized fields.

Non-relational databases, however, excel at working with semi-structured data. This is information that is not organized in fields or records. However, semi-structured data still contains some degree of hierarchy so that you can understand the relations between different entries.

When scaling databases to dozens or hundreds of servers, non-relational databases tend to have better performance. Meanwhile, relational databases are simple and familiar, and perform well for most situations.

Some of the most popular non-relational databases are:

• MongoDB
• Apache Cassandra
• Apache HBase
• Redis
• Neo4j

Meanwhile, the four most popular relational databases are:

• Microsoft SQL Server
• Oracle Database
• MySQL
• IBM DB2

In the rest of this piece, we’ll analyze the pros and cons of two of these relational databases: SQL Server and Oracle Database.

What is SQL Server?

Microsoft SQL Server is an RDBMS developed by Microsoft. First released in 1989, SQL Server now comes in more than a dozen different editions and flavors, each suited for a different purpose and use case. The four main editions of SQL Server 2017 are:

• Enterprise Edition: Includes the core SQL Server database engine as well as add-on services. SQL Server 2017 Enterprise can support databases as large as 524 petabytes (524 million gigabytes), use an unlimited number of processor cores, and access as much memory as permitted by the operating system.
• Standard Edition: Includes the core database engine and standalone services. SQL Server 2017 Standard can support databases as large as 524 petabytes, use up to 24 processor cores, and access up to 128 gigabytes of memory per instance for the buffer pool size. The Standard Edition is missing certain advanced features of the Enterprise Edition, such as Transparent Data Encryption and advanced high availability.
• Express Edition: Includes the core database engine online. SQL Server 2017 Express can support databases as large as 10 gigabytes, use up to 4 processor cores, and access up to 1410 megabytes of memory per instance for the buffer pool size. It is also missing features such as high availability, data integration, and business intelligence.
• Developer Edition: Includes the same features and functionality as Enterprise Edition, but limited by the software license for only development and testing purposes.
  

Each of these four editions is for a different audience. SQL Server Enterprise is for large, high-powered, data-driven organizations, while SQL Server Standard is for organizations with less intensive data needs.

Both the Express and Developer Editions are free of charge. However, they have significant limitations making them unviable as a long-term solution for most organizations.

As one of Microsoft’s flagship products, SQL Server has received constant updates and attention over the years. Recent additions to the platform include new capabilities for performance tuning, real-time operational analytics, big data analytics, data visualization, and hybrid cloud support.

What is Oracle Database?

Oracle Database is an RDBMS developed by Oracle. Like SQL Server, Oracle Database comes in four separate editions intended for different use cases:

• Enterprise Edition (EE): Oracle Database Enterprise Edition is for larger businesses that need a great deal of performance, security, availability, and scalability from their database infrastructure. EE allows businesses to develop high-powered web applications, online transaction processing (OLTP) applications, and data warehouses.
• Standard Edition 2 (SE2): According to Oracle, SE2 “includes all the features necessary to develop workgroup, department-level, and Web applications.” SE2 is largely intended for small and medium businesses, like SQL Server Standard Edition.
• Personal Edition (PE): Personal Edition is for use by a single user on a single machine, and is only available on Windows. PE includes all of the functionality of Enterprise Edition, except for the Oracle Real Application Clusters option.
• Express Edition (XE): Finally, Express Edition is a free-to-use entry-level version of Oracle Database. You can easily upgrade XE to the other versions listed above. XE can store up to 4 gigabytes of data, use 1 gigabyte of memory, and use only 1 CPU.

Most businesses who choose Oracle Database will want to use either EE or SE2. To help you make your decision, the features below are a few of those available in EE but not present in SE2:

• Advanced Replication for one-way and multi-master replication of data in a distributed system
• Advanced Compression for improving performance and reducing your storage footprint
• Oracle Data Guard for high availability, data protection, and disaster recovery
• Parallel computing for index scans, index rebuilding, and backup & recovery

Like SQL Server, Oracle Database has been innovating in recent years to keep up with customers’ demands for processing big data. Oracle Database is easy to integrate with Oracle’s other enterprise software: accounting, enterprise resource planning (ERP), customer relationship management (CRM), etc.

SQL Server vs. Oracle Database: Pros, Cons, and Differences

We’ll now discuss some of the most important points of distinction between SQL Server and Oracle. This will help you come to a final decision that’s right for your situation.

Platform Dependency

As a Microsoft product, SQL Server has traditionally only been available for the Windows operating system. However, beginning with SQL Server 2017, the platform is now available on Linux as well. Note that if you want to use an earlier version of SQL Server, you will have to use it with Windows.

Oracle, meanwhile, has a long history of supporting both Windows and Linux. Oracle Database is also available for other Unix-based operating systems such as Oracle Solaris, IBM AIX, and HP-UX. However, neither Oracle nor SQL Server supports Mac OS X.

User Base

For decades, Oracle has had better scalability and security than SQL Server, making it better suited for large enterprises. In addition, SQL Server was available only for Windows, leaving Linux users out in the cold.

This perception has evolved in recent years. Both Oracle and SQL Server have released mature enterprise and standard versions of their platform, for both Windows and Linux.

Most analysts would agree that Oracle still has a slight edge over Microsoft in terms of both core database features and cutting-edge functionality. However, these extra capabilities have a downside. Oracle is typically more complex to manage, has a higher learning curve, and costs more to maintain.

Language

Both Oracle and SQL Server come with their own “dialect” of the SQL language that extends the basic functionality of SQL. Oracle uses PL/SQL (Procedural Language/SQL), while SQL Server uses T-SQL (Transact-SQL).

As such, database administrators who specialize in Oracle, or in SQL Server, will “speak” a slightly different version of SQL. PL/SQL and T-SQL each have their own distinct features, abilities, and syntax.

The main differences between PL/SQL and T-SQL are how the two languages handle variables, stored procedures, and built-in functions. PL/SQL also allows users to group procedures together into packages, which isn’t possible with T-SQL.

Syntax and Schema

Using Oracle and SQL Server “feels different” not only due to the flavor of SQL, but also due to the many variations in their syntax and schema. For example, because DATE is a reserved word in Oracle but not SQL Server, it’s perfectly legal to have a column named DATE in SQL Server (but not Oracle).

In addition, many data types that are the same under the hood have different names in SQL Server and Oracle. For example, the INTEGER type in SQL Server and the NUMBER(10) type in Oracle serve the same purposes.

These differences are mainly cosmetic, but important if you’re planning a migration from one platform to the other. To see a full list of the differences in syntax, data types, and schema, you can check this page on the Oracle website.

Transaction Control

Transaction control is another important point of departure between Oracle and SQL Server. In a relational database, a transaction is a single unit of work that may contain multiple operations or tasks. So as not to cause discrepancies in the data, the database must either apply or roll back all of the operations in the transaction. A single failed operation will cause the entire transaction to fail.

For example, when customers transfer money between their checking and savings accounts, the transfer should be a single transaction. This transaction consists of three operations:

• Removing the money from the checking account
• Adding the money to the savings account
• Recording the transfer in the bank ledger

If any one of these operations fails, the database must roll back the entire transaction so that the customer’s account balances are correct.

SQL Server executes the operations in a transaction sequentially and individually. This makes it difficult or even impossible to roll back the transaction if an error occurs partway through. The BEGIN TRANSACTION command declares the beginning of a transaction, and the COMMIT command declares the end. Roll backs are not possible beyond the most recent COMMIT statement.

In Oracle, however, each new connection to the database is a new transaction. For most use cases, the database makes changes in memory and commits them only with an explicit COMMIT statement. This is more flexible and allows for greater manual control of errors.

Price and Total Cost

Much about SQL Server vs. Oracle is a matter of opinion, but what’s not up for debate is the price comparison. SQL Server licenses are significantly cheaper than Oracle.

By one estimate, a server with 4 CPUs and 4 cores per CPU would cost $380,000 with Oracle, but just $114,000 with SQL Server. The costs rise even higher with extra features such as table partitioning, data compression, and online analytical processing (OLAP).

While the sticker price may be lower on SQL Server, you should also consider the total cost of ownership over the lifetime of the database. This includes considerations such as support, maintenance, and productivity during day-to-day use.

In this respect, Oracle seems to win out, according to one study of Oracle Database 11g and Microsoft SQL Server 2015. Skilled database administrators can perform typical functions 41 percent more quickly in Oracle than in SQL Server.

These time savings add up over time. The same study estimates that Oracle’s improvements to DBA productivity can save businesses up to $33,000 per DBA per year.

Final Thoughts

In previous years and decades, the question of Oracle vs. SQL Server was easier to answer. With more features and greater complexity, Oracle was better for large enterprises that needed high performance. SQL Server was better for organizations that didn’t want to go all-out.

However, the lines are beginning to blur with newer releases of both SQL Server and Oracle. Both Oracle and SQL Server are mature, excellent choices for relational database management.

In the end, you should choose the right tool for the job in terms of your existing tech setup, in-house skill set, budget, vendors, and customers. Need advice on the Oracle vs. SQL Server question? Contact an experienced database partner who can help give the right advice.

About SQLBot

SQLBot.co offers developers & BI pros a dead simple SQL Slack bot & email reporting tool.   Pop in your SQL and instantly get on-demand reporting  in Slack or scheduled reports in Slack or email.   Give it a try today.