"I am not left handed!" - Inigo Montoya, "The Princess Bride"
One of the first things to cover under this heading would be the concept of a schema. The difference between a schema in SQL Server and Oracle is distinct.
The Oracle 11g Database Concepts manual defines a schema as follows:
"A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes. Schema objects are created and manipulated with SQL.
A database user has a password and various database privileges. Each user owns a single schema, which has the same name as the user. The schema contains the data for the user owning the schema. For example, the hr user owns the hr schema, which contains schema objects such as the employees table. In a production database, the schema owner usually represents a database application rather than a person.
Within a schema, each schema object of a particular type has a unique name. For example, hr.employees refers to the table employees in the hr schema. Figure 2-1 depicts a schema owner named hr and schema objects within the hr schema."
A schema does not necessarily need to contain tables. It contains schema objects - be they triggers, synonyms, views or whatever. Note that the schema is the same name as the user the owns it.
In SQL Server things are a little different. SQL Server Books Online tells us how from SQL Server 2005 and on:
"The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable."
The AdventureWorks database (the demo database that comes with SQL Server) shows this. Inside the one database, there is a schema called Sales, containing all the Sales tables, another called Product for the Products, one called HR for employee data. One user can be granted access to all of these tables (much like in Oracle or any other RDBMS) but they are each contained in their own schema. The schema is just a corral to hold these database objects.
The next piece of the architecture to discuss is how the physical data is stored. In Oracle, the database block size is determined at database creation and can be any multiple of 2K from the smallest of 2K up to the maximum of 32K (although each operating system may have a narrower range that restricts that). In SQL Server, the database block size, call a page, is always set at 8K (although there are exceptions to this, for data types such as varchar(max), varbinary(max), xml, image etc). In SQL Server, 8 x 8K pages forms an Extent which is the smallest allocation unit and should be 8 contiguous pages. Extent allocation in Oracle is handled differently depending on if the tablespace is defined with uniform extent sizes (each allocation is the same, based upon a value set by the DBA at tablespace creation) or is left as system managed (where Oracle decides how large the next extent needs to be based on internal algorithms).
Both Oracle and SQL Server store data in datafiles. In Oracle, one or more datafiles are assigned to a tablespace. (More accurately, when a tablespace is created, a datafile is defined to that tablespace. Later, that datafile can be expanded, or more datafiles can be added to it to increase overall space). Each tablespace has at least one datafile but may have more than one.
In SQL Server, the Filegroup is the concept that most closely matches the tablespace. A filegroup is a logical construct containing datafiles that can be used to spread datafiles out over multiple physical disk devices. When a database is created, a PRIMARY filegroup is mandatory Other user defined filegroups can also be created containing data files on various physical devices. Unless otherwise defined, the primary file group is the default and new objects created without the FILEGROUP keyword are assigned to the default filegroup.
Once again, SQL Server Books Online has an excellent reference to the subject, that I will not replicate here.
The final thing I want to mention in this post is the difference between the transaction logging models.
Oracle instances are defined with redo log file. The redo log contains two or more groups contains 1 or more online redo log files. It is common practice to multiplex the redo log files to protect against media failure. As changes are made to data, logging information is written to the currently active redo log group. When the current group fills, a log switch occurs and log information is written to the next member of the group. This cycle continues through the group as data is generated. If ARCHIVELOG mode is enabled, after a log group is filled a copy of the log is written to the log archive destination. These sequentially numbered archive logs are used for point in time recovery in the event of a failure.
The SQL Server Transaction Log on the other hand is one or more log file per database that is written to continually in a wrap around fashion. In a database in full recovery mode, the transaction log is written to continually and will continue to grow unless a transaction log backup is done. Once the backup is done, the transaction log is free to be written over again from the beginning. In simple recovery model, changes are still logged to the transaction log, but once a transaction is completed, the space in the transaction log is able to be overwritten, so a chain of changes cannot be maintained (point in time recovery is impossible in this mode).
Oracle has two options for logging: ARCHIVELOG mode and NOARCHIVELOG mode. Transactions are still logged to the online redo logs, but the difference here is whether or not the redo logs are archived out to disk when full. The Oracle Documentation discusses this in more detail.
SQL Server has three different logging modes: Full, Bulk Logged and Simple. Full and Simple respectively correspond to the two Oracle methods. SQL Server Books Online covers these in more detail.
SQL Server and Oracle are two different Relational Database Management Systems and hopefully these posts will help explain some of the differences between the two for those people, like me, who regularly are faced with using both.
