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)
