Differences between SQL Server and Oracle Databases

Here is a presentation SQL SERVER vs. Oracle

Started well but toward the end syntax errors became rather obvious. Also some of the Oracle syntax expressions would have run against SQL Server. Again towards the end it was clear the author is more familliar with Oracle - The features comparison missed out on matching SQL Server functionality but also failed to highlight the extra power of PL/SQL.

The choice between SQL Server and Oracle becomes less clear as their functionality converges with each new version. Oracle probably still scales out better, on the other hand the reliability of SQL Server and Windows just improves whilst Oracle is definitly showing signs of being an end of life cycle product.

The FIRST biggest difference: Transaction control. In Oracle EVERYTHING is a transaction and it is not permanent until you COMMIT. In SQL Server, there is (by default) no transaction control. An error half way through a stored procedure WILL NOT ROLLBACK the DDL in previous steps

The SECOND biggest difference: MVCC. In SQL Server and Oracle is different. SQL Server will allow dirty reads, and writes can block reads in MS SQL (Again, it's configurable but the default in SQL Server is for performance and not read consistency, unlike Oracle where read consistency is default and unbendable.

When you setup an Oracle server, you tend to have one database with many "users/schemas", and tablespaces that are shared by all your users. SQL Server has separate databases that do not share disk files.

SQL Server uses "logins" to give you access to the SQL Server instance and each database has "users" that map to a login to get individual access to the tables and views etc.

Typically, all the objects in a database are owned by dbo.

TSQL is similar to PL/SQL, but (in my opinion) less powerful. You may need to simplify your SQL to get it to work as well as you'd expect in Oracle.

The SQL Server Management Studio (2008 SP1) is fantastic!

If you like Oracle, all the "getting under the hood" and "explain plan optimisation" then this training and experience will work well for you against guy's who just code straight SQL Server TSQL and expect the server to perform fast by magic.

SQL Server does not have packages. This might start off as a bonus (PL/SQL packages can be a PITA) but eventually you'll start to get a big nest of similarly named stored procedures in the database and you'll wish there was a way you could organise and group then them better.

To know more click here