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~~~

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.