Subject: Microsoft Sqlserver 7 RDBMS Version: 8.0.5 Operating System and Version: NT 4 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Product Version: Microsoft Sqlserver 7 I am fighting for the continuing existence of Oracle at my shop. Does anyone have technical comparisons between MS Sqlserver 7 and Oracle8? I need all the help I can get. MS has invaded my shop and is basically taking over! Help! -------------------------------------------------------------------------------- From: Oracle, Melissa Holman 27-Jan-00 21:53 Subject: Re : Microsoft Sqlserver 7 Richard, Oracle Support doesn't have a document that would specifically address a comparison between these 2 products. I think a lot of the information available on the Oracle Technology Network website might help though. There is information about why to migrate to Oracle which could also be used as why to stay with Oracle. Also, there is a migration discussion forum at this site that you might be able to get some information through. You could also contact Consulting and/or your sales representative for more specific information. The Oracle Technology Network is a free service but you must sign up for it. The migration information is available at: http://technet.oracle.com/tech/migration/ I will also leave this thread open to encourage other customers to contribute to this issue. Melissa Holman Oracle Support -------------------------------------------------------------------------------- From: Kent Faulkner 25-Feb-00 15:53 Subject: Re : Microsoft Sqlserver 7 Here's a few notes I made from attending a SQL Server 7.0 class: 1. Locking / concurrency SQL Server has no multi-version consistency model which means that "writers block readers and readers block writers" to ensure data integrity. In contrast, with Oracle the rule is "readers dont block writers and writers dont 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). SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment. Also, SQL Server will escalate row locks to page level locks when too many rows on a page are locked. This locks rows which are uninvolved in any updates for no good reason. 2. PERFORMANCE and TUNING a. No control of sorting (memory allocation) b. No control over SQL Caching (memory allocation) c. No control over storage/space management to prevent fragmentation. 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. d. No partioning e. No bitmap indexes f. no reverse key indexes g. no function-based indexes h. no star query optimization i. no hash joins 3. RELIABILITY a. no ability to mirror the transaction log files b. if the logs fill up the disk, the datbase will crash hard. Sometimes requires the server itself to be rebooted. 4. DATA INTEGRITY a. N -------------------------------------------------------------------------------- From: Kent Faulkner 25-Feb-00 15:56 Subject: Re : Microsoft Sqlserver 7 SQL SERVER LIMITATIONS (Con't) 4. DATA INTEGRITY a. No cascade delete option (without writing manual procedures/triggers) b. No optional relationships between tables. FK constraints are only allowed on a mandatory column. 5. MISSING OBJECT TYPES a. No public or private synonyms b. No user written functions c. no independent sequences d. no packages e. no user defined data types f. No "before" event triggers (only "after" event triggers) 6. PROGRAMMING a. Significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a challenge (code re-write). b. No "dead connection detection". Client's who lose their connection may still hold locks until a DBA is notified to kill their server side connection. c. No JAVA engine d. No SQL*Net aliases/service names -- this means applications have to hard code the actual server name into their apps making it difficult to move databases later to load balance across servers. e. Stored Procedures are not compiled until executed (overhead) f. No ability to read/write from external files from a stored procedure. SUMMARY. SQL Server is clearly positioned between MS-ACCESS and ORACLE in terms of functionality, performance, and scalability. I think it makes a strong work group level solution (small number of users with small amount of data). Oracle still is much more advanced and has more to offer for larger applications with both OLTP and Data Warehouse applications. That's my take, and GOOD LUCK! kent kent.faulkner@trane.com -------------------------------------------------------------------------------- From: Oracle, Melissa Holman 25-Feb-00 20:16 Subject: Re : Re : Microsoft Sqlserver 7 Kent, thanks for this information. I am sure other customers will find it useful. Melissa Holman Oracle Support