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.

No comments: