5 ways SQL Server is different than Oracle
Posted: July 25, 2007 Filed under: SqlServer 13 CommentsRecently 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.
Just a simple Hadoop DBA 
Item 4 isn’t quite correct – at least using temporary tables in one way but a more appropriate solution would be to use a ref cursor datatype, either weakly typed like the built in sys_refcursor or a strongly typed variable of your own.
>DDL commands (like update)
DML (Data manipulation language) commands.
BTW one can write instead of triggers to do DML on views.
>4. Functions.
There are ref cursors to get the similar functionality.
Some more listed @ psoug.org;
Microsoft SQL Server 2005 Comparison – 24-Apr-2007
http://psoug.org/reference/sqlserver.html
Miall,
It seems that sys_refcursor really gives the functionality I was thinking of, and I didn’t even know it exists.
Thanks.
Gints,
You are right, of course – DDL was a typo.
Regarding functions – ref cursors has to be defined in advance, so it won’t work if your code only decides on runtime which columns to return. But it seems that sys_refcursor will do the trick.
H.Tonguç Yılmaz,
Thanks for the reference. I remembered I saw it once, but I didn’t bookmark it, and couldn’t find it again when I wrote the post.
The entire PSOUG site is just full of useful references.
about nr 4, you could also use a pipelined function to return a resultset. This way you can issue a select on a function which return multiple rows.
I think it is not hard to understand how the others make perfomance tuning on SQL Server. As it is SQL Server the expectations are not the same as using Oracle. So you feel yourself happy when everthing runs without any unreasonable crash 🙂
It would be better if you share how you migrated to Oracle. Like which tools and ways you used.
Ibrahim,
I thought that our migration process simply isn’t going to interest anyone. On the other hand, I did waste about 6 month of hard work on the migration and related issues, so maybe it is worth a post…
Thanks!
Freek,
I definitely learn something new every day with this blog.
Its the first time I hear about pipeline functions, and it looks like a really great idea. Makes so much sense, and can really be useful in many applications.
The only downside I see is that just like with ref cursors, I need to know what the table will look like when I write the code. I can’t choose columns during runtime.
@ibrahimdemir
Just FYI I have an article about data migartion from old to new app here http://www.gplivna.eu/papers/legacy_app_migration.htm
It wasn’t only from SQLServer but from various sources including SQLServer.
@prodlife
I think it is worth to write articles about success scenarious especially pointing out any mistakes so that other people can probably avoid them. You know smart people learn from other’s people mistakes, normal people learn from mistakes of themselves, fools don’t learn at all. We of course are smart people, aren’t we? :))))
prodlife,
you will always want to know the columns when you use pipelined functions in Oracle. Otherwise you won’t be able to cast he output (which you must do when you actually pipe the row output).
You _can_ choose columns at run time when you select from a pipelined functions. Those will be the attributes of the type you declared.
Raj
No. 3. ‘In SQL Server, if a view has a set operation like “Union All”, you can still apply [DML] … to the view itself instead of the underlying tables. The tables will update automatically.’
When you say ‘automatically’ I thought to myself you mean Magically! – how does the server know which tables the row(s) should be inserted into?
I Googled briefly and it seems the view would have to be a ‘partitioned view’ whereby the underlying tables would have to hold mutually exclusive data – i.e one table has USA customers another has French customers. Seems to be a mechanism related to increasing performance.
Interesting. Enjoyed reading your blog recently. Thanks very much.