Saturday, January 14, 2012

SQL Server vs. Oracle Differences Part 2 (Object Naming Differences)

There are a couple of key object naming differences that I noticed when working with Oracle for the first time. The first is the max character difference. In SQL Server, an identifier (table name, view name, stored procedure name, etc.) can have a max of 128 characters. In Oracle, the max length is 30 characters, except for a few cases. Coming from SQL Server, the 30-character restriction in Oracle was quite noticeable. I especially noticed it when naming stored procedures. I've found myself having to abbreviate words when naming Oracle stored procedures instead of spelling words completely out.

Another difference with naming appears to be somewhat related to the GUI tools. With SQL Server, if you create table Orders, SQL Server Management Studio (SSMS) will display it as Orders. In Oracle, however, a table created as Orders will display in the GUI as ORDERS. This occurs because nonquoted identifiers, like Orders, are not case sensitive with Oracle. Oracle interprets them as uppercase. Thus you'll see the table you created as Orders listed as ORDERS when looking at the database through a GUI tool like Toad or SQL Developer.  Quoted identifiers, such as "Orders" are case sensitive, but then you are required to include the quotes when referencing the object. Since nonquoted identifiers tend to be more popular when naming common objects such as tables, views, stored procedures, etc. I found this important to note.

Oracle's upper-casing interpretation of nonquoted identifiers has resulted in a somewhat common practice of using underscores when naming objects. For example a SQL Server stored procedure SelectProductsByCategory might be named SELECT_PRODUCTS_BY_CATEGORY in Oracle. In this case we have a 27 character name, which is just barely under the 30 character limit. If we wanted to rename SelectProductsBySubCategory to SELECT_PRODUCTS_BY_SUB_CATEGORY we would be at 31 characters which is 1 beyond the max. The use of underscores, in this case, is what pushes us above the 30 character max.

Fortunately, from a querying perspective,  the casing of object names doesn't seem to be as relevant. That is, of course, dependent upon the settings. Assuming your SQL Server database is created with a Case Insensitive collation, SQL Server will not require specific casing when querying your objects. If you create table Orders, both "SELECT * FROM Orders" and "SELECT * FROM ORDERS" will be valid queries. With Oracle (using the default settings), "SELECT * FROM Orders"and "SELECT * FROM ORDERS" would both be valid, as well.

There are, of course, more details on SQL Server and Oracle naming rules than I've been able to put into my post. SQL Server's naming conventions are available here. Oracle's naming rules are available here.

Tuesday, January 10, 2012

SQL Server vs. Oracle Differences Part 1 (Cursors)

Since my first job out of college, where I was working with SQL Server 6.5, I've been told that cursors are BAD, BAD, BAD! Cursors are evil! I was, eventually told the reason to be performance. In SQL Server world, cursors are still considered evil in most situations. I would submit that T-SQL code with a cursor would be considered "bad code" by many in SQL Server world, assuming there wasn't a code comment explaining the use of cursor. I'm not saying this is right or wrong, I'm just submitting what I would expect would happen.

Now get ready for it, with Oracle PL/SQL, cursors are often a BEST PRACTICE! This was, probably, the hardest best practice difference for me to grasp. Cursors are very much accepted as good practice in Oracle world. You'll even see them used quite often in Steven Feuerstein's Oracle PL/SQL Best Practices book that I found to be very useful.

SQL Server vs. Oracle Differences From a Developer's View

Over the past 2 1/2 years, I've been doing a lot of software development with Oracle as the RDBMS. After a decade of only working on development and BI projects with a SQL Server back end, I found myself primarily working on a quite large, already existing web application that uses Oracle as its RDBMS. Don't get me wrong, I'm still involved with the world of SQL Server. I still see it at work and, even more, I'm President of our local PASS chapter. I also did two years of speaking on SQL Server at various events and user groups during this time period.

As a software developer, though, I've found working with Oracle to be a great opportunity to become familiar with another leading RDBMS. When I started my current job, I was told that I'd be able to pick up Oracle quite quickly, given my strong data background. I'd like to think that I picked things up pretty quickly, but there are A LOT of best practices differences and, also, some capability differences that I needed to become aware of as a developer. Since, I haven't seen a lot of resources that put these differences together into one place, I decided to start this blog post series.

I hope to make this blog post series a resource for developers who are switching between T-SQL and PL/SQL. This is not intended to be a "which RDBMS is better?" conversation. I personally believe that there are multiple RDBMS solutions that have the capability to meet a variety of business needs. I hope this series will help developers shorten their learning curve between the two products. Note: At the writing of this post, SQL Server 2008 R2 is the latest officially released version of SQL Server. I have been keeping up with the upcoming T-SQL features for SQL Server 2012, though.