#0154–SQL Server-Database Object Names and Naming Conventions for Identifiers


Haste makes waste – this proverb holds true today also. However, if you think about it hard enough, you may find that haste also teaches us new things, or as it happened recently with me, reminds us of things we already know.

I generally prefer the use of the keyboard to perform most of my tasks because I find that it works much faster for me. Therefore, to execute a query, I use the F5 key instead of pressing the “Execute” button. A couple of days ago, I was working on a query to diagnose a data issue and in my hurry to execute it, I pressed the F5 and the “5” key together (the number 5 is just below the F5 key if you observe).

Obviously, GIGO (“Garbage-In-Garbage-Out”) happened. The query was accidentally modified and this is what was seen:

USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,
       OrganizationLevel,
       5JobTitle              -- Notice the accidental placement of "5" here
FROM HumanResources.Employee
GO

#0154 - Results

So, What Happened?

What happened here is actually, quite simple. As you can see, SQL Server took the number “5” as the value, and the rest of the string as the column alias. This is because the column alias is a database object name and all object names must follow the rules identified for identifiers.

Database Identifiers

Object identifiers are used to uniquely identify the object. Identifiers comply with the following rules:

  1. Identifiers must start with one of the following:
    • A letter as defined by Unicode standard 3.2 (includes a-z, A-Z and letter characters from other languages)
    • The underscore sign (_), at sign (@) or the number sign (#)
  2. Subsequent characters can be:
    • Letter as defined by Unicode standard 3.2
    • Decimal numbers from basic Latin or other national scripts
    • The underscore sign (_), at sign (@), the number sign (#) or the dollar sign ($)
  3. The identifier must not be a T-SQL reserved keyword

  4. Embedded spaces or special characters are not allowed

  5. Supplementary characters are not allowed

In our case…

In our case, the column alias was accidentally altered to become “5JobTitle”. Because a column alias is an identifier and cannot start with a number, SQL Server automatically set the value to be “5” and the column name to be “JobTitle”.

Possible solutions

The solutions are many, including:

  1. Remove the erroneous number in the column alias
  2. Use the AS keyword and define a column alias by using:
    • Double quotes
      USE AdventureWorks2008R2
      GO
      SELECT BusinessEntityID AS "BusinessEntityID",
             OrganizationLevel AS "OrganizationLevel",
             JobTitle AS "JobTitle"
      FROM HumanResources.Employee
      GO
    • Square braces
      USE AdventureWorks2008R2
      GO
      SELECT BusinessEntityID AS [BusinessEntityID],
             OrganizationLevel AS [OrganizationLevel],
             JobTitle AS [JobTitle]
      FROM HumanResources.Employee
      GO

Reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx

So, that was a reminder for me to slow down before I furiously start typing on the keyboard.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s