Oracle Vs SQLServer

Hi Friends..

Didn't realise my question would spark so much controversy.

I have to admit that I have been involved with SQLServer a little lately due to one of my projects and have noticed how 'easy?' it is to operate, purely because it is a GUI - but you cannot be as anlytical with SQLServer as you can with Oracle and I have only been looking into it for the past few days.

From what I have been reading lately and although I am 100% Pro Oracle - I have decided to look into SQLServer a little more as a contingency - especially as I have around 35-40 working years in me and technology waits for no-one.

Thanks for your input into this discussion.. I put the sqlserver guy in his place for now but for how long, only time will tell.................

Cheers

Suresh

Suresh,

I am promoting neither SQL Server nor Oracle. As a Tech Guy I am pointing out the differences between SQL Server and Oracle. They are:
1 Dirty Reads
SQL Server supports dirty reads. It means data changed by first transaction can be seen in the 2nd transaction even though the first transaction has not committed or rolled back the entire transaction. Example in SQL server:
In the Query Analyzer, enter the first transaction:
Begin transaction
Update EMP set deptno = 60 where deptno = 10
Now open another Query Analyzer for the 2nd transaction:
Select * from EMP
If you try to run 2nd SQL statement, then it won’t be executed because the previous transaction has not yet committed or rolled back. SQL server will wait till the completion of 1st transaction because of the isolation level is set to read committed.

Now, add (NOLOCK) to the Select statement.
Select * from EMP (NOLOCK)
This statement is immediately executed and the result set would display the updated rows from the first transaction. This is called dirty read in SQL Server.
Where as Oracle did not support dirty reads. For the 2nd transaction Oracle uses pre-images of the data being modified by first transaction from the rollback segments.
Bank transaction needs dirty reads that are possible in SQL Server but not in Oracle.
2 Isolation Level
SQL Server is designed for higher isolation level.
The level at which a transaction is prepared to accept inconsistent data is termed the isolation level. A lower isolation level increases concurrency but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can negatively affect concurrency. Oracle is just opposite of SQL Server.


3 Partitioning Table.
Table level Partition is not supported in SQL Server 7. If you have a big table (say 1 B rows), then you have to manually create small tables (say 10 tables with 10M rows), and use a view that has UNION of all small tables to access the full table. This is not the case in Oracle. Oracle supports Range, Hash and composite partitioning.
4 Image data (BLOB, CLOB etc )
Maximum size is SQL Server 2 GB
Maximum size is Oracle 4 GB
5 Columns per Index
In SQL Server 16
In Oracle 32

6 Outer Join
SQL server supports full outer joins (left and right) where as Oracle supports one way outer join only. More over it works just opposite in both RDBMS. For example, in SQL Server the left outer joins statement looks like:
Select * from emp a, dept b where a.deptno *= b.deptno
If you want the same result set in Oracle, you need to write as given below:
Select * from emp a, dept b where a.deptno = b.deptno (+)

7 Set Operator DIVIDEBY (DIVISION)
Both RDBMS do not support DIVISION set operator. I have been waiting for this facility since 1985. I do not know why it is not supported in Oracle as well as in SQL Server.

8 Platform availability
SQL Server is available only on NT / W2K OS where as Oracle is available in almost all platforms.

9 Administration
It is easy to administer SQL server because of its size and its GUI tool. Oracle needs experienced DBA to administer a very large database.

10 HOT BACKUP
Both RDBMS support hot backup.

11 Replication
It is supported in both RDBMS.

12 SEQUENCE
Sequence is defined at the table level in SQL Server, where as it is a separate object in Oracle.

13 24x7 Availability
Both RDBMS can be run 24x7 as long as the hardware is good. I had seen many big Oracle databases crashed because of the poor hardware setup. At the same time I had seen medium sized SQL Server databases running continuously (24 x 7) on a very good Compaq and IBM servers.
14 Rollback Segment
No more Snapshot too old error message. Oracle finally is going to get rid of this concept in 9i. Has any one tested? In 9I, only a tablespace would be designated for rollback area. No need of creating Rollback segments. There is NO Rollback Segment concept in SQL Server. It uses LOG file for its roll back activity.

I have pointed out the tech details of both RDBMS. Okay. Which one is superior? I leave the answer to DBAs. If your database is going to be small, and only few concurrent users use the database, and you do not want to spend more money, then go with SQL Server. On the other hand, if your database is going to be big, and you have a large user population, then choose Oracle. If you want to build a Data Warehouse of size over 3 TB, then go with Teradata.

PS: I am currently working on a conversion project from SQL Server to Oracle.

[Edited by tamilselvan on 06-20-2001 at 12:54 PM]

Excellent, "non-partisan" comparison!

That was one good comparison. http://www.dbasupport.com/forums/


I'm awaiting for this thread become stable, such that I can go ahead and document it.

Keep going ...
Sam

In the early days of SQL Server they had page level locks instead of record-level locks. When you went to update a record, it locked all the other records on that same "page" (similar to Oracle's db block). Does anybody know if this is still true?

It has been changed now. SQL Server 7 supports row level lock as well as page lock and table lock.

That's a indepth reply Tamil.

Cheers.

As tamilselvan wrote :
14 Rollback Segment

No more Snapshot too old error message. Oracle finally is going to get rid of this concept in 9i. Has any one tested? In 9I, only a tablespace would be designated for rollback area. No need of creating Rollback segments. There is NO Rollback Segment concept in SQL Server. It uses LOG file for its roll back activity.

In 9i you can use SMU ( System Managed Undo ), which uses an undo-tablespace instead of RBS.

But I think there is a hidden danger.
If you have a transaction that generates lots of RBS-data, only the transactions that use that RBS will maybe fail if the RBS becomes to big ( no more space in TS, max_extents reached... )
If we use 9i SMU, will it not be possible that 1 transaction fills up the entire UNDO-TS, and so blocking the whole db ?
This could also happen when using undo_retention= for using flashback query's.

I also have the feeling that lot's of my fellow dba's are a bit sceptic about SMU. They don't trust it quiete well.

Anyone an idea about this ?

Regards
Gert

PS if more suitable, a moderator can make a new thread of this reply. ( but I put it here because it's a reply to an answer in this thread. )