The Great Database Battle – SQL Server vs Oracle Database
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
The two most famous RDBMS (Relational Database Management Systems) with the largest market shares are o Microsoft SQL Server and the Oracle Database.
They have a lot in common but also a lot of differences between them.
Let's see in detail:
 |  |
|---|---|
| Filegroups: It is a set of datafiles which define a group in which we can store objects such as tables. The default filegroup is called primary. | Tablespaces: Tablespaces do a similar job. The default tablespace where all objects are stored is called USERS. |
| The smallest storage size is the Page (8K) and is stable. | We have changed db_block_size depending on the use we want. We usually set a smaller size for transactional databases and a larger one for data-warehouses. |
| Fill Factor: It is the percentage that one is allowed to fill Page in an Index (default behavior is at 100%) and is important to avoid page splits. This space is reserved for future inserts and updates. | PCTFREE: is the percentage one is allowed to fill Block. This space is reserved for future updates and cannot be reserved by inserts. High Water Mark: It is the boundary between free and occupied space in a set of data blocks / pages (segment). As its size increases, so does the HWM. HWM is not reduced by deletes but only if a table truncate, redefinition, shrink or deallocate. |
| Schema: It is a logical group that allows the separation of database objects such as the tables. Each schema belongs to a database user. As default schema we have dbo. | User/Schema: Each user who creates an object is under his schema and in order for another user to see it, he must have the corresponding rights. As default schema we have users. |
| Autocommit: Whenever we make a transaction such as e.g. an update of a record is done by itself commit (save). *unless it is inside an explicit transaction (begin tran). | No auto commit: Every time we do a transaction we should commit or rollback to complete it. |
| select creates blocking with the default isolation level (read committed): When a user selects a table and at the same time another user updates the same table, those records will be blocked until the first user finishes. If there is a third user, it will have to wait for the second one and so on. | select does not create blocking (with the exception of select for update): Due to its architecture (with the undo tablespace) that part of the block from the datafiles is read or updated during a select, the original image of the blocks remains in the undo tablespace. So in the case of changes, the data is read by undo and thus there are no blockings. *When we have the well-known Error ORA-01555 contains the message, “snapshot too old.” means we should give more space to the undo tablespace. |
| Temporal Tables: It is the technology that enables row versioning in the table so that we can call past queries. For example: SELECT *, [SysStartTime],[SysEndTime] FROM [dbo].EMP FOR SYSTEM_TIME AS OF '2020-05-04 09:30:00' | Flashback query: The corresponding technology in Oracle is the flashback query which works similarly. For example: SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP('2020-05-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') |
| It doesn't have a modern one Active/Active solution but now in an asynchronous form has the Azure Data Sync. As Active/Passive has the technology Always On Availability Groups and Failover Cluster Instance. | Active/Active Clustering with Oracle Real Application Cluster (Oracle RAC): One of the most powerful cards that allows you to have multiple nodes connected to the same disk array for simultaneous access read/write. |
| Log shipping with Norecovery: We can have as a disaster recovery solution a server that will send the logs to another where the database will remain closed until we want to open it. | Passive Data Guard: Corresponding technology. |
| Always On Availability Groups / Log shipping with standby / Transactional replication: Disaster recovery solutions with one server sending the information to other servers, with the difference that the database can be open for reading readonly on remote nodes. | Active Data Guard: Corresponding technology with capability readonly. |
| Full Backup: It is the backup of the entire base. | Level 0 backup: It is the backup of the entire base. |
| Differential Backup: It is the backup of the blocks that have changed since the last full backup was taken. | Level 1 backup: It is the backup of blocks that have changed since the last level 0 backup was taken. |
| Transaction Log: It records all the transactions that have taken place in the database. | Redo Log: It records all the transactions that have taken place in the database. |
| Transaction Log Backup: It is the transaction log backup. | Archived Log / backup: It is the redo log that has been archived by the archiver service. In archived logs we can call one archived log backup. |
| Simple Recovery Model: When the database is in a simple recovery model, the transaction log can be rewritten without having been backed up. This has the effect that the database cannot be restored to a point in time. | Noarchivelog Recovery Model: It is the equivalent in terms of the redo log with the difference where when the base is in noarchivelog in Oracle, it does not have the possibility to take a backup of the database online. In this case, the database should not be accessible in order to take a backup. |
| Full Recovery Model: When the database is in full recovery model, the only way to keep the transaction log from filling/growing is with a transaction log backup so that the existing one can be reused. In this recovery model there is the possibility of restore point in time | Archivelog Recovery Model: When the database is in archivelog recovery model the only way to keep the redo log from filling up is with an archivelog backup so that the existing one can be reused. In this recovery model there is the possibility to restore point in time |
| TSQL: It is an extension of the SQL language from Microsoft. They have differences in structure as well as in behavior e.g. a null value with an empty text ' ' is different | PLSQL: It is an extension of the SQL language from Oracle. In Oracle in the same example a null value with an empty text ' ' is the same since it is converted. |
| Snapshot Database: We have the ability to be able to save the database image at a specific point in time, so that in case many changes are made and we need to go back immediately without having to restore, this is possible. | Flashback Database: Correspondingly in Oracle, we can get a guarantee restore point so that in case many changes are made and we need to go back immediately without having to restore, this is possible. |
| Server Memory: It is the memory set for use by the instance. | SGA+PGA: Memory is divided into System Global Area and Program Global Area. The first is common for all sessions, while in the second each session has its own. |
| bcp in / bcp out: It is a SQL Server application with the ability to export and import data from tables using command lines. | impdb / expdb: They are Oracle applications with the ability to export and import various objects such as tables, procedures, meta-data, indexes, etc. It can be used from command lines. |
| The primary key becomes a clustered index key of exile: When a field is defined as primary key, the table with clustered index (btree structure) is automatically created in this field. | There is no default clustered index behavior. Tables with primary keys can be heap. When creating it, it should have the organization index parameter (for a corresponding btree structure). |
| sqlcmd: It is the implementation of SQL Server so that database queries can be managed through command lines. | Sqlplus: It is Oracle's equivalent application so that database queries can be managed through command lines. |
| instance: A resource set with multiple databases | instance: A set of resources with one database each |
| Indexed views: Unlike simple views – which point to real tables/views – indexed views have their own physical existence. After creating them, a clustered index should be created in the view so that it is considered indexed. | Materialized views: They work like the indexes views, with the difference that their creation is done easily with a single command. |
Of course, it is not possible to make a complete comparison in one article. An attempt has been made to cover the general picture of the main differences between the two databases.
Each of them is recommended for use depending on the situation. They also have quite different pricing policies.
This is a very insightful and informative article, I work with both relational databases and this piece has made me know the clear similarities in both SQL server and Oracle database.
Thank you for the support!
Nice