Design database from Oracle to SQL2K5
-
Hello everybody,
I'm going to explain briefly the situation I'm in and I'd like some feedback from those who have experienced similar situations.
We have an application (developped in-house) that was built for Oracle 10g (it's a standard J2EE application). The code of the application was made so that if we wanted to support another DBMS, we would be able to. Granted we would not be able to take fully advantage of all features since there is a codebase that is common (some DAO calls, etc...). I'm not writing here to argue that the application should be solely designed around 1 DBMS and that trying to support more than 1 is asking for trouble. I've heard that before and it's just not possible when developing applications because clients just won't always be running/willing to purchase the one DBMS you support...
Sooo, my question is regarding the development of the DBMS for SQL 2005 (no 2000 support, 2005 only). My main "concern" is regarding the use of schemas. I know that SQL2005 supports schema in a similar way than Oracle. But if I try to compare an Oracle database and an SQL database, I see it this way:
- Oracle: 1 database, many schemas because one database is a very self-contained set of processes etc...
- MSSQL: More than one database to avoid ownership headaches and all databases running are contained in one main process (provided they all run on the same instance).
Has anybody been able to play with MSSQL schemas and can tell me a bit if there's anything I should be careful with that is different from Oracle's way of handling schemas?
I'd like to think that all the new features in SQL2005 (synonyms, schemas, recursive queries etc...) will make this transition very easy but I would like some feedback from people who have been there and done it. Mostly, is trying to convert each Oracle object to its SQL equivalent always a good idea (knowing that we have some triggers and packages)? We also have some base data that we load using sqlldr with some format files.
If I'm not very clear please let me know, I'm not sure why I'm so worried about it myself but I don't want to start playing with a feature thinking that because it has the same name as its oracle counterpart it should behave the same way and figure out it does not 5 days in. I will be (in fact I currently am) reading the docs regarding this but I also would like people's opinion.
Thanks,
Greg
P.S: I will take a look at Microsoft's SQL Server Migration Tool from Oracle 10g but I want to do most of that work myself so that I can understand and control the result.
-
This is a big subject, but I'll try to touch on some of your concerns. This isn't a comprehensive list or discussion, it's just off the top of my head...
Synonyms in Oracle and SQL Server are very similar, you should have no problems using them in SQL if you are familiar with Oracle.
Schemas are fairly close, but there is one major difference. In Oracle, Schemas are the same as Users, any tables in a given schema are also "owned" by that user. In SQL this is not the case. Schemas are completely independent from users and the existence of a table in a given Schema says nothing about ownership. Tables exist in schemas but are owned by users, and while the two might be the same, they aren't necessarily.
Common Table Expressions in SQL are very similar to the CONNECT BY clause in Oracle, but the syntax is different.
Some gotchas: Oracle treats an empty string as NULL, SQL does not.
Oracle sorts Nulls at the bottom, SQL sorts them to the top (assuming ascending ORDER BY here)
Oracle Unique constraints allow NULLS (Plural), SQL Server allows exactly ONE.
Oracle is funny when it comes to case sensitivity, and in any case uses a case preference in sorting. You can get the same behaviour out of SQL if you use a collation that is not case sensitive, but does use case preference.
Whenever possible stick to the ANSI Standard functions that they have in common I.e. use Current_timestamp instead of sysdate or getdate(), use coalesce rather than nvl or isnull etc...
Always use the ANSI JOIN syntax for both. I've had to slap down some developers who can't seem to get away from the Oracle (+) syntax.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I forgot to mention that if you use SQL Server's new Read Committed Snapshot transaction isolation level, SQL Server will act nearly identically to Oracle when it comes to transactions and concurrency, with no changes to code.
Spend some time learning SQL Server's security model. It's quite different from Oracle, but I'll just say don't confuse Logins with Users in SQL. They are two very different things and lots of folks use the two terms interchangeably, but that's a mistake that leads to much misunderstanding and grief.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi,
Thanks a lot for that info. I'll try to keep in mind that schema difference, it might be a concern for me.
Regarding case senstivity and null strings, we've already had this problem with other applications and have been able to go around it fairly easily. Note that in a win32 application written in a language such as delphi, strings are not objects, hence an empty string and a null string are the same thing, which is not for the database (at least SQL) but ends up giving the same result during data access. However, in Java prepared statements this is certainly not the case so I'll be looking out for these...
Right now, the biggest thing that concerns me out of your comments is "Oracle Unique constraints allow NULLS (Plural), SQL Server allows exactly ONE." I'm going to look at this one very closely to make sure we have not made that assumption...
For the old Oracle syntax, no worries, we actually only started thinking about Oracle when it started supporting ANSI JOINS
Thanks a lot for these, I'll keep them in mind!
Greg
-
Some gotchas: Oracle treats an empty string as NULL, SQL does not.
is that always true? My memory is that we finally developed two sets of code because oracle would switch back and forth between the two in minor releases. Glad to see they've finally settled.
Oracle is so big... seems like anything you say about it can be questioned over time
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I don't recall Oracle ever NOT treating empty strings as NULL. I'm not saying that it's not possible, but I've never seen it.
I'm not aware of any setting that changes that behavior in either SQL or Oracle.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply