Comparison of SQL Server and Oracle terminology

By:   |   Updated: 2015-04-30   |   Comments (6)   |   Related: > SQL Server and Oracle Comparison


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:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-04-30

Comments For This Article




Wednesday, November 29, 2023 - 9:42:45 AM - Mavuri Sathish Chandra Bhanu murthy Back To Top (91779)
Thank you Daniel... good work..

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

Thanks fot your volunteership.

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

Thank you Daniel... good work...

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

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 (37070)

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 (37069)

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.














get free sql tips
agree to terms