Question: I am an Oracle DBA who is
charged with implementing a SQL Server database. What are the
differences in terminology between MS-SQL and Oracle? I also
understand that SQL Server is far less flexible and robust than
Oracle and I hope to know those features that I will not have in SQL
is the world's most popular database primarily because it runs on
every platform known, from a mainframe to a Mac. MS-SQL on the
other hand, is a Windows-only offering. Both SQL
Server and Oracle are relational databases, but it is their
genealogy that tells about their core differences:
- SQL Server: MS-SQL grew-up running
tiny, departmental applications on personal computers.
Microsoft is trying to "push up" into the mainstream market,
running SQL Server on super-large servers that run Windows.
- Oracle: Oracle is a mature database
with a decade head-start on MSSQL in terms of specialized
features. While SQL Server has tried to "push up" into the
Corporate database market, Oracle has made "simplified" releases
of Oracle (i.e. Oracle XE) to "push down" into the realm of
departmental computing to complete with MS-SQL.
Also see these related notes:
Why does Oracle have to be so hard?
One of the most common questions asked by a SQL Server DBA is "Why
does Oracle have to be so hard?". Any robust and flexible
database must, by definition, be complex, and Oracle is complicated
because Oracle allows you to fully control every aspect of RAM and
disk storage. Unlike SQL Server, Oracle allows you to control
exactly where rows are placed on the data blocks and control almost
every internal machination of the RAM regions.
The complexity of the database engine also reflects in the
salaries for Oracle DBAs vs. SQL Server DBAs. As of 2015 the
average salary for an
experienced Oracle DBA was over $100,000 per year, while a SQL
Server DBAs earn a fraction of that salary.
Here is a visual of the differences between Oracle and SQL
Oracle is the Swiss Army Knife of relational databases.
SQL Server is lean and easy to use with lots of
SQL Server has only a
few dozen tuning knobs while Oracle has hundreds of
This makes SQL Server less robust, but
far easier to use.
A comparison of SQL Server and Oracle Terminology
While both Oracle and SQL Server are ANSI compliant databases,
they are vendor products and the developers have a vested interest
in making their database competitive. Some of the terminology
differences between Oracle and SQL Server include:
- Logical data storage terminology: In
many RDBMS applications, the Device Media Control Language
(DMCL) layer provides the mapping between the physical data
files and the logical table/index storage areas. Oracle,
DB2 and other RDBMS products call this logical storage a
"tablespace", but SQL Server uses the term "page" and "file
group" to refer to a unit of storage.
- Data Blocks: Both SQL Server and
Oracle map their physical data blocks to the logical storage.
Because Oracle runs on UNIX, block sizes can range from 2K to
32K, while SQL Server, being constrained by Windows, must use 8
K blocksizes (8k "pages").
- Listener process: Oracle uses an
independent daemon process called a "listener" to manage
incoming connects to Oracle. SQL Server has no listeners.
- Object-data block mapping: Both SQL
Server and Oracle allow a single data block (page) to contain
rows from different tables, for faster I/O. In Oracle this
is implemented via
cluster tables. Clustering
related rows (e.g. order rows adjacent to item
rows) all items for an order can be fetched in a single I/O.
Special features: As noted, both
Oracle and SQL Server try to remain ANSI compliant while still
making their databases "special". Hence there
within Oracle (CTAS, decode) that do not exist in MS-SQL.
There are also significant differences in the SQL dialects.
||System ID (SID)
||fixed at 8 pages
management pages (SMP)
||dictionary or local
||connect by clause
HierarchyID data type