SQL Server VS. Oracle
-
well Oracle runs on UNIX ... that's the major difference.
MS SQL Server doesn't.
If only they had been ordered to be split from OS and products - then there might be a MS SQL Server for UNIX - but that'll never happen.
-
quote:
well Oracle runs on UNIX ... that's the major difference.MS SQL Server doesn't.
If only they had been ordered to be split from OS and products - then there might be a MS SQL Server for UNIX - but that'll never happen.
Doesn't anyone read the other posts in this thread? As I said earlier, only Oracle-pro people see a disadvantage in SQL Server only running on Windows. Microsoft see it as a strategic choice and a big advantage, because this means that SQL Server can take advantage of a lot of features in the operating system without having to figure out how to do this in other OSes.
And as for the 'Unix is much more stable than Windows'-argument, this is really not the case any more. Maybe, but I am definately not sure, it is generally a bit more stable. But this comes at a significantly higher TCO, and Windows/SQL Server can definately be a very stable system if configured and administered correctly. Over a year and a half ago I was on a project where we redesigned the Swedish White Pages Online-application from a Unix/Oracle/Apache/JSP-system to a Win2K/SQL Server/IIS/ASP-system. It has not been down even a second since, and during this time it has even been moved to a different physical location.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I have experience of both RDBMSs, for me they both serve their purpose very well and there is less and less that separates them in terms of functionality. The high end clustering seems to be clearly owned by Oracle, but few people run this, PLSQL and Java offer powerful DB programming which I am not convinced are matched by TSQL. I have 20 Oracle instances and about 3 SQL ones, and SQL locks, and the programmers and DBAs are working hard to stop this, but it still locks. I am fairly sure that things like this will affect scalability. This has affected performance and application availability. As far as instance availability and normal running is concerned I am pleasantly surprised by SQL server and cannot see it as a real differentiator.
The real difference for me is the administration philosophies that the products histories have imposed on them. Oracle's ability to run on lots of platforms has forced it to be far more open, you need lots of settings if you are going to adapt to all these platforms. This makes it more complicated but more flexible, more scriptable, more manageable in volume. You may have seen an internal memo from MS that outlined the problems it would face hosting hotmail on its OSes, they were just not as easily manageable in bulk, had useless scheduling and scripting. I fear this is still the case with windows based apps. Now things are improving and there are loads of things you can script in SQL server but MS is all about Wizards. These are good 90 % of the time but many an MS admin I have meet has never gone beyond them, so even if there are windows scripting solutions they are rarely used.
Wizards shield the administrator from the underlying architecture and stop people thinking out of the box. Try customizing an SQL maintenance plan.
A person familiar with UNIX will soon start scripting, and as soon as they get on a windows box they install cygwin or MKS. For the large organization with real 24/7 Unix/Linux + Oracle is still ahead.
Where MS gets it’s foot in the door it is with the developers, it is still easier to install (just) and administer (just) and Oracle still scares people, this perception will take time to dispel. SQL server is also very well integrated to the MS programming suites. This is probably why Oracle’s internal programming is so rich because Oracle can’t really tell people to write VB apps.
Both RDBMSs have good management consoles and tools, SQL server is friendlier out of the two for SQL tuning, the SQL profiler and Index tuning wizard are really useful and increase the access to developers. Oracle has Java and that is where it’s at, even if C# is a copy intended to facilitate poaching of java coders, it’ll make conversion both ways easier.
Price is no longer a differentiator you can even rent Oracle, it’s like the management tools, something that is no longer the case.
The choice should really be about what you do with your database and if you have many of them. Small departmental databases in small companies are perfectly served with MS SQL, they’ll be up quicker and they’ll be easier to manage by jack of all trade techies. Both serve DSS systems very well, but again simplicity will see you go to SQL for smaller projects. If you want something that’ll scale both in terms of instance numbers, sizes and support 24/7 global activity then Oracle is still the one.
Let’s hope both product continue to compete as they have both improved greatly.
-
quote:
Not being too familiar with PL/SQL, can you give an example? I've heard it is a more powerful languange, but not any specifics.Steve Jones
I have worked with Oracle and SQL server and although both are good, I feel that Oracle's stored procedure language and trigger handling is superior.
Oracle has before and after triggers which is much better than an insteadof trigger. It is easy to write a single general trigger that can capture the modifying user and the modified date of any table.
Premodification values and postmodification values are handled more elegantly in Oracle. Using old.<field name> gives you the before modification value and new.<field name> gives you the post modification value.
You can differentiate between editing and inserting with an if inserting or if updating clause. In t sql there is no way to tell if you have inserted or updated.
User functions have existed in Oracle for a long time and provide full functionality.
We have attempted some very complex queries and have had success with them in Oracle but not in SQL server.
SQL server has the edge in ease of administration. In my experience, you very often couldn't get reasonable performance from Oracle queries without frequently running explain plans and providing optimizer hints. Much more DBA intervention is necessary to keep Oracle humming than is required for SQL server.
SQL server also allows access to the operating system from T Sql while Oracle does not.
Oracle definitely has the edge in locking. The locking scheme in SQL server is complicated and often gets in the way. Most of the time you have to settle for dirty reads. Oracle has its own problems with rollback segment management and snapshot too old issues.
SQL server is better integrated into .NET if you are using this technology for front end development.
I haven't found major differences in performance. Overall SQL server seems to be a more economical solution.
I don't understand why people feel that SQL server couldn't scale to handle large systems. Both databases are good products. For a pure database, I would go with Oracle, but for .NET integration, SQL server is the way to go.
However, DB2 should be brought into the discussion because I have been hearing very good reports about its capabilities.
-
quote:
Not being too familiar with PL/SQL, can you give an example? I've heard it is a more powerful languange, but not any specifics.
Steve Jones
I forgot to mention that Oracle has elegant exception handling which is missing from T sql.
-
quote:
SQL server also allows access to the operating system from T Sql while Oracle does not.Just a nitpick here--I believe Oracle does allow access to the o/s with the HOST() built-in in pl/sql (and probably in lots of other ways w/java).
Cheers,
-Roy
-
One point I have not seen, is that how well Oracle and SLQ Server work depend largely on the people involved.
You have to have people that know what equipement to buy, how to configure it for the DB and App. Then it is up to the DBA's to configure and setup the database correctly and tune it to the hardware and app. Bad setup in either one will lead to disappointing performance if the application is going to push the hard ware performance capabilities. Then of course you typically get the most improvement in application performance by tweaking/tuning the application and the database statements themselves. The knowlege of the people working on the project have a far greater impact than which database you put on a piece of hardware.
Overall, SQL Server has been good in the small/med size projects. Cost effective, but typically requiring more hardware per end user than Oracle. Easy initial setup and dependable.
Oracle has a larger price tag, but we have been able to get more throughput on less hardware. The additional cost also buys multiple forms of tech support and complete freedom to run what ever version of Oracle you want. If you are running 7.0 and pay maintenance, there is not addition software costs to go to 8,8i, or 9i.
I like both. Each project we have is evaluated on the requirements for that application. Sometimes it is SQL and other times it is Oracle. We run 38 Oracle servers and 18 SQL Server servers. Most boxes run multiple copies of the databases and some run mulitple versions.
I have enjoyed this discussion.
Joseph
-
This is definitely one of the longest threads I've seen yet. Could only make it through the first page.
I've used Sybase, SQL Server and Oracle. I learned Oracle on a job where I had to do everything. At that point I had only work in a development utilizing Sybase. I found Oracle to be a huge learning curve and the documentation, though robust, was a lot to take in when all I wanted to do was set up a simple database, communicate with it and get some code written.
Sybase is pretty simple from a development standpoint. Definitely not as robust as Oracle but I never came across anything I couldn't do.
SQL Server is definitely my favorite db these days. I still develop within Sybase, our Oracle is totally gone now. I work in a development role but am able to easily hack my way through the DBA functions. I would imagine, though, the more you get into tuning... the more involved the effort would be. I also really like the fact that you can interact with the windows environment and have found DTS to be an extremely useful tool with SQL Server itself and also across db platforms. The only downside is as someone mentioned earlier, security. Not that Microsoft is any worse than the other platforms but it definitely is the one targeted by most hackers which means they have to raise the bar that much higher.
If I never have to work on Oracle again I certainly won't be shedding any tears.
-
quote:
Doesn't anyone read the other posts in this thread? As I said earlier, only Oracle-pro people see a disadvantage in SQL Server only running on Windows.Nope ... sorry - I've never even used Oracle. So, how can I be Pro Oracle ?
I use MS SQL and I use Unix OSes. The Unix OSes don't have anywhere near the number of security issues and memory leaks that MS servers have.
Perhaps, I should just quote the bug in MS's ISA (Firewall) - now that's scary.
-
I have worked as a developer with both Oracle and SQL Server, and here are a couple of other thoughts.
Oracle has bitmap indexes. Primarily for data warehousing apps, these can be applied to columns with low cardinality (few unique values compared to number of rows) to provide much improved performance for queries against those columns. There is nothing comparable in SQL Server. Those closest thing might be to use a partitioned view, but that takes a lot more admin effort.
Oracle has SQL*Loader, which is superior to bcp, but limited compared to DTS.
PL/SQL is superior to T-SQL. Aside from exception handling, mentioned previously, PL/SQL has much better support for cursors and arrays. In my opinion, this lets you develop cleaner code, without the need to use temp tables as is typical in T-SQL.
Finally, there are some excellent third-party tools for Oracle. For example, Platinum (now CA) SQL-Station contained a PL/SQL debugger as well as an Explain Plan utility that provided a breakdown between statement parse time and the execution steps in seconds, as opposed to the cryptic "cost" provided by SQL Server. SQL Server includes a good set of tools, but this makes it less likely that third-parties will develop great tools (or that you will be able to justify the expense of getting them if they are out there.)
-
quote:
I use MS SQL and I use Unix OSes. The Unix OSes don't have anywhere near the number of security issues and memory leaks that MS servers have.Yes, I agree that there are many more security announcements for MS OSes than Unix. However, if you set up your system correct and monitor security lists these should not really be that big an issue. Security is definatley not limited to patching holes. The problem is that MS has made it so easy to administer Windows and SQL Server that many people think they don't need to do anything other than running some wizard.
I am sorry for misunderstanding your points on operating systems that the systems run on, normally what people mean when they say this is that Oracle has an advantage in that it supports many platforms. This is what I meant when I said that only those who like Oracle better than SQL Server see this as a disadvantage for SQL Server.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Hello folks,
Good topic discussion. dovidf said:
>>>
Oracle has before and after triggers which is much better than an insteadof trigger. It is easy to write a single general trigger that can capture the modifying user and the modified date of any table.
Premodification values and postmodification values are handled more elegantly in Oracle. Using old.<field name> gives you the before modification value and new.<field name> gives you the post modification value.
You can differentiate between editing and inserting with an if inserting or if updating clause. In t sql there is no way to tell if you have inserted or updated.
>>>
You certainly can tell the difference between an update & insert trigger in SQL Server. Postmod values are available in triggers and are in a system table called inserted (the equiv to Oracle's new) & premod are in deleted (again, the equiv to Oracle's old). An insert trigger will have a zero count deleted table, an update trigger will have non zero count inserted & deleted tables. A delete trigger will have a zero count inserted table. Based on the above logic it is easy to write a single trigger that handles all three cases.
Allowing access to the OS may or may not be a good thing. Useful if implemented properly with correct security procedures. Some wish list items for me (and I think Oracle supports some if not all):
- Exception handling in TSQL
- Configurable page and extent sizes at the database level, if not the table level
- Cleaned up locking behaviour
Overall, SQL Server is a good product that we've had great success with. Some systems we've written run 6/24 (that's right, Sundays are off) and any support calls I've had were not database related (application, network probs, etc).
Vik
-
I've worked on custom legacy migration as part of new product implementation in SS for several years and in the last 6 months have join a conversion project for a different product on Oracle.
For the SS projects, we use a series of individual T-SQL Update/insert/delete statements to do the entire conversion from the legacy tables into the destination tables. These statements are executed from QA and embedded checks can be added to ensure the data is transformed correctly. So, the process is totally interactive, but allows me to concentrate more on the data itself rather than spending time compiling and debugging the code.
For the Oracle project that I joined, the existing conversion code uses explicit cursors almost exclusively, so I'm back to debugging looping problems and development of new code takes 2-3 times as long (you can likely attribute that to my coding skills). I see there is some debate in the Oracle community over the use of implicit (just a single SQL statement) vs explicit cursors and I've found that there are some simple T-SQL statements that just don't work as PL-SQL implicit cursors and require additional coding.
Most of the posts here seem to be for developers creating stored procedures and there are valid concerns about locking behavior and error handling. However, for quick on-the-fly data manipulation to get custom one-time implementation projects done, SS is a clear winner for me.
-
quote:
You certainly can tell the difference between an update & insert trigger in SQL Server. Postmod values are available in triggers and are in a system table called inserted (the equiv to Oracle's new) & premod are in deleted (again, the equiv to Oracle's old). An insert trigger will have a zero count deleted table, an update trigger will have non zero count inserted & deleted tables. A delete trigger will have a zero count inserted table. Based on the above logic it is easy to write a single trigger that handles all three cases.
Allowing access to the OS may or may not be a good thing.
You saved me from having to point out the same......However, I would like to inject my two cents as well:
I have worked extensively in both enviroments, and to date, have found no functions that cannot be duplicated in one or the other. The methodology may be different, the language different, but the solutions provided in either platform can be done in the other as well, though perhaps using different ways of going about it.
The enviroments arguement may very well be valid, and if you are privelidged enough to be able to choose your enviroment, I could see using this arguement, but in 17 years, I've never had that opportunity, as the business had it's enviroment chosen way before the database choice was made. So, I don't see that as a pro or con, but simply a choice of OS...
And come on, How many businesses actually worry about the cost of ownership, unless it's a major difference. I've watched many many businesses go through the decision, and it almost always ended up a political decision, rather than a monetary decision. The numbers can be manipulated quite readily either way. I have watched companies carefully evaluate office supply companies to save 50 cents on staples, while paying three times the money needed outsourcing dba work to prevent the overhead of hiring a dba at 1/3 the price. It's all in how it's presented.....
In my mind, the main difference is not the database, but how it's used, who programmed for it, and what they knew or didn't. Oracle has been taken much more seriously over the years, and they never went the direction of trying to eliminate the need for dba's (hence no DTS, statistics, or fixed extent sizes, and much better error handling, and perception of being a high end server solution). However, in defense of SQL Server (I AM PRO SQL SERVER), error handling is not an issue if your server doesn't hit errors. (Oracle guys throw errors on purpose, with the intention of handling them, where as SQL Server guys try to eliminate errors from occurring) and in all reality, in my experience, they actually perform at a comparable level when either are programmed by competent dba's, understanding the differences and knowing how to avoid the gotcha's.
So, If I got to say why I like SQL Server over Oracle..........it's the milliseconds that are supported by SqlServer
. No other reason, just the milliseconds........ -
Scorpion, thank you so much for the best post in this quite lengthy thread. Shows it was actually worth following the thread through 5 pages...
In response to your last statement regarding milliseconds, this is exactly what I'm talking about. Everyone has something that somehow makes them like on product over another, and this means that any nitpicking done on details on the other will always be made from a viewpoint that "this is how it's done in my product, so therefore that is the best way to do it".
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
. No other reason, just the milliseconds........
Viewing 15 posts - 46 through 60 (of 96 total)
You must be logged in to reply to this topic. Login to reply