Mazharuddin Ehsan (7/2/2009)
See some more useful comparisons in the below links:
Be careful - some of the comparisons are not entirely accurate!
For instance, it says that Oracle has compressed indexes, but SQL Server 2008 does not. That's not true - SQL Server 2008 does row and page compression for both tables and indexes. Suprisingly, it also says there is no equivalent for the CONNECT BY statement, which isn't true because a recursive CTE does the same thing... and is probably more compliant to ANSI SQL than the Oracle equivalent. And there is a COMMENT equivalent in SQL Server, I know because my firm needs to use it. I might be missing something here because it's under the trigger section.
I'd also be interested in hearing what people have to say about the following comment:
SQL Server has a totally different internal structure than Oracle has. In SQL Server, a table is basically a big linked-list and the data blocks are essentially the leaf-blocks of the cluster index. Those blocks are then doubly-linked back and forth so you can traverse the table in a full table scan or in an index range scan. In fact, an index range scan of the whole table is essentially (physically) the same as a full table scan. I'm not sure exactly why, but SQL Server has always had trouble with corruption of these link-list pointers. Run DBCC to check (and fix) problems with these pointers.