SQL Server Vs Oracle


I have worked with both Oracle and SQL Server since 2009.  In my previous position at Motorola Solutions, I had the great opportunity to work on both environment.  Listed below are some of  their key features but not the complete list.

SQL Server 
  •  Utilizes TSQL Statement.
  • One instance with  multiple database. 
  •  Utilizes Linked Server to other RDBMS and Excel which  can allow  limited PLSQL Statements when connecting to Oracle based platform .
  •  Has two types Triggers which are   After and Instead.
  •   Data Manipulation  can be extracted or altered using Datepart(), DateDiff(),   DateAdd() or  Getdate().
  •  User Defined Functions(UDF)  can return datatype or table.
  •  Stored Procedures are executed.
  • Tables can be created using the  Create table statement or Select into Table_Name from Table_Name, view_Name or UDF_Name statements.
  • Simple XML(SQL Server 2008)  features (  XML AUTO, XML RAW , XML EXPLICIT etc) to transform data both structured and unstructured data.
  • Identity column to generate unique number and Sequence(2012 ).
  • Materialized views in Oracle is called indexed views.  The major difference between indexed view and regular view is that in the view statement creation schema binding is included and after the view has been created, one or more columns are indexed. 
Oracle (10g & 11g Releases )
  • PLSQL Statement 
  • Oracle includes Flashback and Savepoint 
  • It is very case sensitive with objects name created wit double quotations.
  •  Utilizes Public Links to communicate to different RDBMS using ODBC or to other Other Oracle Database.
  • Oracle allows one Instance per Database( 10g and 11g) but one instance may contain multiple tablespaces which can be allocated to various users or applications. 
  • Utilizes Tablespace to store data.
  • Data Manipulation can be executed using  To_Char, To_Number,   To_Date, To_Timestamp  to extract  specific information from a table or System tables.
  •  Utilizes Not Null constraint to define specific condition for a table column.
  • Constraints can be deferred for large data loading only if the data has been tested prior in staging environment with same rules to make sure they are no errors.
  • Indexes created during the primary key creation in a table creation can be named using the Create Index statement. 
  • Utilizes Sequences to generate unique numbers.
  • Tables can be created using the Table definition statement or Create table table_name as query statement.
  • Conditional and unconditional multiple insert statements.
  • Triggers ( Before, After , Instead)
  • Records, Package, Oracle Application,  Label and Oracle Security( Row level security etc).
  • Identity based column for unique number generation were introduced with Oracle 12c
  • BLOB, CLOB & NCLOB datatypes for large data storage. 
 Each platform has their limitations but can be best apply to any environment.

Comments

Post a Comment

Popular posts from this blog

ETL ( Automation vs Manual )

ETL applications like Talend, SSIS and Pentaho can simplify ETL or Data migration by eliminating or reducing the amount of SQL coding needed to complete the process. However same functions performed by ETL applications can be performed by scripting using various dialect of SQL and programming language like C# or Java. The downside of scripting manually without an ETL application is the need of connecting to data source and destination source. However most vendors like Microsoft SQL Server provides developers the option through linked server using SQL Server Management Studio or Oracle MySQL Workbench for MySQL.  Depending on the application a third programming language is need as interface like Java or C++ when performing the process from a developmental application. This will allow both the source and destination sources to be linked.  Another problem with the manual process is the complexity of SQL coding needed which needs to be altered constantly and the length of the co...
Read more

Data Migration

Migrating data  can be a challenging  task depending on the size of the data, platform and systems involved with the migration process. The age or the nature of data can also add to the complexity of the migration process. Legacy data or old data needs to be re-validated and cleaned to prevent any major issues when the data has been migrated just like you have with Data Warehouse system. ETL applications like Talend , Pentaho and Microsoft SSIS can be utilized in this process. The structure of the data plays a major role in determining the schema and the layer of the data.Texted based systems and RDBMS have different properties that needs to be examined and changed. The final properties to be examined is the platform. Migrating data from SQL Server 2008 to SQL Server 2012 is much easier than migrating from SQL Server 2008 to Oracle 12c or 11g due to different datatypes, objects , schema and properties. In some cases the underlying objects like ...
Read more

SSIS, SSAS & Stored Procedure

Image
In the following series SSIS would be utilized to extract data from Stored Procedure which contain query that is linked with SSAS MDX query via linked Server. Talend requires additional components while SSIS has fewer components but the output is the same. First process in extracting the query through SSAS via Linked Server is to create a linked server. Linked Server Second process is to create the stored procedure to extract the data. Stored Procedure definition with the MDX and regular SQL qeury Third process is to create an SSIS package. In the Control Flow component you need  two task which the Execute SQL Task and Data Flow task.  The fourth step is to create the database connection to the source and destination systems. In this case only one database system is being utilized. Another big difference between SSIS and Talend is that Talend  works well with JDBC but JDBC is allowed in SSIS rather .NET, OLED, ODBC etc which makes t...
Read more