Comparison – Understanding Tables Between Oracle and SQL Server

Comparison - Understanding Tables Between Oracle and SQL Server sqloracle 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.

Solarwinds

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.

OracleSQL Server
Heap-organized tableHeap
Clustered tableIndexed views
Partition tablePartitioned table
Temporary tableTemporary table
External tableLinked server
Object tableTable type
Index-organized tableClustered index
N/AIn-Memory OLTP table
Hybrid columnar compressed tableColumnstore 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)

Solarwinds
,
Previous Post
SQL SERVER – The NOLOCK Question – Notes from the Field #117
Next Post
SQL SERVER – Installation Error – File Format is Not Valid

Related Posts

12 Comments. Leave new

  • Derek Bodendorfer
    March 11, 2016 8:25 am

    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

    Reply
  • I would add here also Oracle’s Nested table ~ XML datatype in MSSQL.

    Reply
  • Pinal, Oracle have InMemory option too.

    Reply
  • 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.

    Reply
  • Houri Mohamed
    May 22, 2016 1:02 pm

    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]

    Reply
  • Andrea Caldarone
    June 21, 2016 9:27 pm

    Oracle equivalent of SQL Server Indexed viiew is “materialized view”

    Reply
  • Andrea Caldarone
    June 21, 2016 9:33 pm

    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)

    Reply

Leave a Reply

Menu