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.

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.

SQL Saturday Oklahoma City, August 27th

I am very excited to be on the Steering Committee for SQL Saturday Oklahoma City! We're sold out with 250 registered, but we're hoping to be able to include those from the wait list.

We've got DBA, Developer, and BI tracks planned. I look forward to seeing those registered. As the Speaker Chair, I very much hope everyone enjoys the sessions:)

For those wondering where I've been on the blog front, I've been spending most of my community time with regional community involvement. OKCSQL assisted with three SQL Server Boot Camps in 2010. We're, also, still meeting monthly at the OKCCoCo. Plus, I've been helping plan SQL Saturday OKC. As for speaking, I'm currently on sabbatical. My last speaking engagement was at Tulsa Tech Fest 2010.

I hope to see you guys at SQL Saturday OKC!

Tulsa Tech Fest 2010 Presentation

Back in November I had the opportunity to give my latest presentation Intermediate SSAS 2008 - Developing Time Calculations which is available here.

SSIS Scripting

Tim Mitchell did a great job presenting on Scripting in SQL Server Integration Services this past Monday to OKCSQL. A video of his 90 minute presentation is available here.

T-SQL Intersect and Except

During the SQL Server 2008 Database Development Boot Camp in February, several T-SQL features introduced in SQL Server 2005 and 2008 were covered. I wanted to blog about the one that most caught my eye. SQL Server now has both Intersect and Except which follow the below syntax.

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Examples from the AdventureWorks database for SQL Server 2008 follow:

SELECT COUNT(*) FROM Sales.Customer
(Returns count of 19185)

SELECT CustomerID FROM Sales.Customer
INTERSECT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 19119 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in BOTH Sales.Customer and Sales.SalesOrderHeader)

SELECT CustomerID FROM Sales.Customer
EXCEPT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 66 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in Sales.Customer that are not found in Sales.SalesOrderHeader)

I've heard debate as to whether or not INTERSECT and/or EXCEPT are faster than other options. Thus, I'll leave it up to you to try them out in your own systems to determine their potential value. I will mention, though, I have heard of a real-word case where an INTERSECT query similar to the one in the above example took seconds or minutes instead of hours. In that specific scenario the table on the left side of the INTERSECT had just a few thousand records and the table on the right side of the INTERSECT had millions if not billions of records.