5 ways SQL Server is different than OraclePosted: July 25, 2007
Recently I had to move an application from SQL Server to Oracle. I knew the databases are different, but I had no idea how different. Here are few issues I ran into:
1. Dates. Thats the most obvious difference. SQL Server dates are cast into strings implicitly, developers don’t need to do anything. Date arithmetics is done differently, and date functions are completely different. If you do anything interesting with dates in your code, you are better off writing it from scratch for each DB. Trying to translate the different functions can get you into trouble.
2. Partitions. SQL Server 2005 supports partitions, but before that SQL Server developers were stuck with creating lots of identical tables and putting them in a view to simulate partitions. When you translate an application to Oracle, you probably want to use real partitions instead.
3. Views. In SQL Server, if a view has a set operation like “Union All”, you can still apply DDL commands (like update) to the view itself instead of the underlying tables. The tables will update automatically. Unfortunately, by version 10.2, Oracle still doesn’t support this functionality.
4. Functions. In SQL Server a stored procedure returns the result of the last query used in it. So basically procedures return a table, just like a query. It was quite useful. In Oracle you will need to use temporary tables for a similar functionality.
5. Wait events. SQL Server doesn’t allow you to trace event 10046. Actually it doesn’t even give the normal SQL Trace functionality that Oracle has. No TKProf either. It does have a profiler – but it is not nearly as detailed as what Oracle provides. After working in Oracle, you can no longer understand how anyone managed to do any performance tuning with SQL Server.