Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Comparison of SQL Server and Oracle terminology


By:   |   Last Updated: 2015-04-30   |   Comments (5)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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.


Learn more about SQL Server tools