Comparison of SQL Server and Oracle terminology


By:   |   Updated: 2015-04-30   |   Comments (5)   |   Related: More > Other Database Platforms

Problem

There are several terms that are similar in SQL Server and Oracle. However, there are other terms that are different and require some clarification. This tip was written to clarify terms in Oracle and SQL Server for users who need to work in both worlds.

Solution

What are the SQL extensions in SQL Server and Oracle?

Oracle

SQL Server

PL-SQL (Procedural Language - Structured Query Language). T-SQL (Transaction Structure Query Language).

What does package mean?

Oracle

SQL Server

In Oracle, a package is a group of different PL-SQL subprograms and items. It is used in PL-SQL. In SQL Server, a package is a group of tasks used by SQL Server Integration Services. It is used to automate tasks, integrate objects and can be created visually or using programming languages.

What database files are used?

Oracle

SQL Server

In Oracle you can have tablespaces that have the logical storage. Physically, the data is stored in data files. There are also other files used called control files used to operate the databases. You also have the Redo log to register database activities. In SQL Server the primary Datafiles have the mdf extensions and are used to store data. There are also secondary datafiles (with ndf extensions also used to store Database information). You also have the Log files with ldf extensions to store the database logs. The datafiles are grouped in Filegroups.

What is an instance?

Oracle

SQL Server

An instance is a set of memory structures used to administer data files. You can have several instances in one server. In Oracle an instance and a database are closely related. One database has at least one instance. An instance is a service that runs as an operating system service. It handles the system and user databases. You can have several instances in one server. In SQL Server, an instance has many system databases and can have one or multiple user databases.

Is there a list of system tables?

Oracle

SQL Server

Yes. You can find a list here. In SQL Server, most of the information is stored in views (we call the views catalog views). You can find a list here.

What is a data dictionary?

Oracle

SQL Server

The data dictionary is a set of read-only tables that store information about the database. In SQL Server, the term data dictionary is used differently. The database objects can be retrieved using the system stored procedures and a data dictionary is an explanation of the tables, columns, etc.

What are the system databases?

Oracle

SQL Server

In Oracle the instance and the database are closely related, there are not system databases like in SQL Server.

In SQL Server each instance has the system databases which includes:
the master database which stores the system information,
the model database which contains a configuration template for new databases created,
the tempdb database used for temporary storage or temporary results,
the msdb database which contains the SQL Server Agent configuration
and the resource database which contains system objects included in SQL Server.

What are the dynamic performance tables?

Oracle

SQL Server

In Oracle, the dynamic performance tables are read only views used to identify performance problems in the database. The prefix used is V$ . In SQL Server the name of these views (and functions in the Microsoft case) are Dynamic Management views and functions. The prefix used is sys.dm_. Beware that the Dynamic Management views include other views that are not used for performance purposes.

Where are changes in a database registered?

Oracle

SQL Server

In Oracle it is uses the Redo Log . It consists of 2 or more files used to stored database changes as they occur. The Redo Log is used to restore the information at a specified moment.

In SQL Server the changes are stored in the transaction log file. It has the same purpose as the Redo Log.

How can I store the information temporarily?

Oracle

SQL Server

In Oracle, it uses a tempfile. You can create a temporary table space used to store temporary data.

In SQL Server, the data is stored in tempdb which is a system database used for this purpose.

Is there a database service for the Cloud?

Oracle

SQL Server

Yes, there are several companies that offer Services in the cloud, but Oracle also offers services. For more information, review this link. Oracle is offering not only databases, but other services in the cloud like ERPs, CRMs, etc.

For more information about these services, refer to this link.

Yes, there are several companies that offer Services in the cloud, but Microsoft also offers these services. The cloud in the Microsoft world are in the Microsoft Azure Portal. Azure is the key word for the Microsoft Cloud Services. You can have Linux, UNIX, Microsoft Machines in Azure.

What tools are used for business intelligence?

Oracle

SQL Server

There are several tools used for BI. In Oracle they are called the OBIEE (Oracle Business Intelligence Enterprise Edition) products:

Yes, there are several BI tools:

Are there command line tools to access databases?

Oracle

SQL Server

Yes. You can use SQL*Plus.

Yes. You can use sqlcmd or PowerShell.

Next Steps
For more information about SQL Server and Oracle, refer to these links:

Last Updated: 2015-04-30


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources





Comments For This Article




Saturday, August 22, 2015 - 4:03:03 PM - chaptal Back To Top

Thanks fot your volunteership.


Thursday, April 30, 2015 - 5:10:13 PM - stone age coder Back To Top

Thank you Daniel... good work...


Thursday, April 30, 2015 - 12:22:08 PM - Jeff Bennett Back To Top

Good article and a great idea to expose end-users to the difference in terminology.    I get requests from senior-level developers who request a new installation of SQL server (instance), when, in fact, all they need is another database on a current server.   Requests for new databases often resolve into a need for a new table in SQL server.  

 

We even have enterprise-level software that does not cope well with both Oracle and SQL documentation because of the difference in meaning for the terms database and instance.  

 

Thanks, 

 

Jeff Bennett

St. Louis, MO


Thursday, April 30, 2015 - 10:00:04 AM - Daniel Back To Top

Thank you Laslo. I will have more articles comparing both databases.

I will include your questions in later articles.


Thursday, April 30, 2015 - 2:36:37 AM - Laslo Back To Top

Thanks for this post, very informative! However, I would also be keen on understanding the difference between Oracle and MSSQL on object and deeper technical levels such as memory/buffer handling,  and last but not least, pricing as of a certain date on configurations of similar robustness.



download


Recommended Reading

SQL Server and PostgreSQL Linked Server Configuration - Part 2

Creating a SQL Server 2014 Linked Server for an Oracle 11g Database

SQL Server and PostgreSQL Foreign Data Wrapper Configuration - Part 3

Transferring Data Between SQL Server 2014 and Oracle 11g Databases

Comparing some differences of SQL Server to SQLite





get free sql tips
agree to terms


Learn more about SQL Server tools