When I wrote the blog around my understanding, a couple of my friends from the Oracle side asked me if I can write some of these blogs that can expand to other comparisons too. I know some of them can be complex and this is a point in time understanding. Some of the differences may be different over the year as Oracle and SQL Server evolve over the years with next versions happen.
I am taking a stab at a basic object structure that one can create inside SQL Server Tables. Tables are the primary objects that store data in rows and columns. To improve performance and extend functionality, each DBMS offers different types of tables that support different access needs. In a nutshell, I see the following observations.
Oracle and SQL Server Comparison
Oracle and SQL Server have different terminology regarding these constructs:
- Oracle has a Heap-organized table which MS SQL calls “Heap”
- Oracle’s “Clustered” tables are called “Indexed Views” in SQL Server
- Both databases have “Partition” & “Temporary” tables
- Oracle’s “External” tables are called “Linked Servers” in SQL Server.
- Oracle’s “Object” table is called “Table Type” in SQL Server.
- Oracle’s “Index-organized” tables are referred to as a “Clustered index” in SQL Server.
- Oracle does not have an equivalent/term for SQL Server’s In-Memory OLTP table
- And Oracle’s “Hybrid columnar” compressed tables are called “Column store clustered index” in SQL Server.
Rather than having it in textual format, it would be a great way to put the same in a table format will help.
| Oracle | SQL Server |
| Heap-organized table | Heap |
| Clustered table | Indexed views |
| Partition table | Partitioned table |
| Temporary table | Temporary table |
| External table | Linked server |
| Object table | Table type |
| Index-organized table | Clustered index |
| N/A | In-Memory OLTP table |
| Hybrid columnar compressed table | Columnstore clustered index |
I think I have covered most of the components of using tables. If there are some things that I am missing, let me know. These are user defined tables and not system generated tables. Have you ever used any of these in unique ways – let me know via comments below. Would surely love to learn from your experiences too.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
These traslations between oracle and sql terminology are helpful. When I was first introduced to an Oracle environment, coming from Sql Server, the two biggest things that caught me off guard were
1. How all the objects in oracle were organized under the user/schema. I was using DBeaver as my gui at the time and it took me a minute to figure out how to navigate through the objects, whereas in sql sever I historically would simply click on the Tables folder in SSMS for example.
2. The other thing that tripped me up was the importance of the TNSNAMES.ORA configuration file! I’m a dev/analyst and never had to manually mess with a sql server config file directly to ensure my connection bits where in place and proper.
Once I had those 2 pieces down it was easy breezy to make the switch to Oracle. However, now I am back with Sql Server, but I appreciated the opportunity to get some Oracle experience
Thanks Derek for sharing.
very good
Thanks badr gamal
I would add here also Oracle’s Nested table ~ XML datatype in MSSQL.
Great. I didn’t know that.
Pinal, Oracle have InMemory option too.
Great! Thanks for sharing.
I don’t think Oracle’s External Table is equivalent or similar to SQL Server’s Linked Server. We do have External Tables introduced in SQL Server 2016 and as I understood from a recent conversation with experts, the new External Tables are one Polybase way of dealing with Big Data.
There is no such Oracle Hybrid columnar compressed table. An Oracle table can be either BASICALLY(free of licence) compressed or OLTP (need a licence) compressed. The HCC is a compression technic which, if my memory serves me well, initially implemented in the Oracle Exadata machine. Columnstore Clustered Indexes are something completely different and could not be compared with HHC.
I don’t know exactly what SQL server Indexed views are but It is fairly likely something which is different from an Oracle Cluster table.
And finally one of the readers wrote that Oracle Nested table are XML datatype. This is probably not correct as an Oracle Nested table has nothing to do with an XML datatype.
Best regards
Mohamed
[url removed]
Oracle equivalent of SQL Server Indexed viiew is “materialized view”
Hello Emito,
same name but very different feature:
Oracle “in-memory” is essentially a non clustered column store index that the db engine build and maintain on the fly backing normal row-store data.
SQL Server “in-memory” OLTP is a lock and latch free data structure that exist only in memory to improve OLTP performances.
Oracle advantage is that you have not to modify your code to benefit of the improevement.
SQL Server advantage is that you can exploit both columar data store and lock latch free structure but at one cost: you have to re-design your db (for column store index) or both the db and the application (for in-memory OLTP)