Oracle and SQL Server: Basic Terminology Differences

Oracle Database and Microsoft SQL Server are two of the most commonly used enterprise-level relational database systems (RDBMS)world-wide. A developer or database administrator competent on one of the systems may assume that knowledge in one will naturally carry over into the other; however, this is only partially true. While basic concepts remain the same across all RDBMSs, the vocabulary used to express those concepts can vary enough to cause significant confusion. Further, any attempt to compare and contrast Oracle and SQL Server will be faulty if terminology definitions are not clarified first.

Below are a few of the most important terms and what they actually mean in the two systems.

ORACLE
Database: Physical files used to store information: Data files, control files, redo-log files.

Instance: Memory processes, etc, used to access data in the database.

Together the database and the instance make up the installation of the Oracle product. There can be multiple installations on a single computer. There can also be one installation across multiple computers.

Schema: Collection of objects such as tables, views, procedures, etc. It is associated with a single user.

User: Someone who can connect to the Oracle Database and a particular schema. There is always a 1 to 1 relationship between a schema and a user. The database administrator can assign privileges to a user via pre-determined roles.

SQL SERVER
Instance: The installation of the SQL Server product. Each installation contains both the needed files and the needed memory processes. When SQL Server is installed, four default databases are created, which can be thought of as similar to the control and data files in the Oracle Database.

Multiple installations are possible on a single computer.

Database: Collection of related objects such as tables, procedures, views, users, etc. There can be multiple users in a single database.

Login: This is the name and password combination used to connect to the instance. It does not necessarily mean the person can connect to any databases as well. That is accomplished via a mapping to a user. A login can be assigned roles, such as DBA, which will grant various privileges across the full installation.

User: The user is stored within a database. More than one user can work within a database, and more than one can have ownership rights to the same database. The user is assigned various roles, such as data reader, data writer, database owner, etc.

When moving from one database environment to another or deciding between the Oracle and Microsoft database products, it is vital to learn foundational terminology early on to avoid confusion and miscommunication. The terms above are just the beginning of the significant differences between the two systems.

SOURCES
Blog article
Microsoft's White Paper
Oracle 's White Paper
DevX Article:
Database Journal
http://www.adp-gmbh.ch/ora/concepts/users.html