COMPARE MS SQL SERVER VS
ORACLE 10G
SQL Server:
Pros:
Its easy installation, self-tuning capabilities, graphical administration,
integrated business intelligence and extensive collection of help wizards
The total cost of ownership (TCO) of SQL Server 2005 is lower than that of
Oracle
Specialized index on a computed column
Indexed View
Cons:
Locking and concurrency: SQL Server has no multi-version consistency model,
which means that "writers block readers and readers block writers" to
ensure data integrity
a. DBA has no "real" control over sorting and cache memory
allocation. The memory allocation is decided only globally in the server
properties memory folder, and that applies for ALL memory and not CACHING,
SORTING, etc.
b. All pages (blocks) are always 8k and all extents are always 8 pages (64k).
This means you have no way to specify larger extents to ensure contiguous space
for large objects.
c. In SQL Server, no range partitioning of large tables and indexes. In Oracle,
a large 100 GB table can be seamlessly partitioned at the database level into
range partitions.
With SQL Server 2005, INSERT, UPDATE, and DELETE statements are executed
serially (MERGE is not supported).
Oracle:
Pros :
you can use Oracle on multiple platforms. Whereas Microsoft created SQL Server
to be used on the Microsoft platform only, Oracle is available on multiple
platforms, including Windows, Unix and now Linux, which is the foundation of
Oracle's Real Application Clusters (RAC) strategy.
Locking and concurrency: "readers don't block writers and writers don't
block readers." This is possible without compromising data integrity
because Oracle will dynamically re-create a read-consistent image for a reader
of any requested data that has been changed but not yet committed. In other
words, the reader will see the data as it was before the writer began changing
it (until the writer commits).
function-based indexes
Oracle will execute INSERT, UPDATE, DELETE, and MERGE statements in parallel
when accessing both partitioned and non-partitioned database objects
Cons:
"Implementation of something similar to MSSQL Identity by using Oracle
sequence would require reflecting the sequence name in the application or
creating a trigger for each table/sequence pair.
Cost is higher
Required skilled DBA.
CONCURRENCY MODEL
concurrency control. The main differences are summarized in the table below:
Oracle Database 10g SQL Server 2005
Multi-version read consistency Always enabled. Not by default.
Must be enabled.
Non-escalating row-level locking Yes Locks escalate
read committed with snapshots (statement-level read consistency)
snapshot isolation (transaction-level read consistency)
These isolation levels correspond to Oracle’s READ COMMITTED and SERIALIZABLE
isolation levels, respectively.