Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sam Vanga

Sam Vanga helps business people solve their data problems and enables them make data-driven-decisions. His specialties include data integration, data warehouse and database development, and BI design. Sam blogs at http://SamuelVanga.com.

Standardize Table Aliases

What's wrong with the following code?

  1. SELECT
  2.     a.[BusinessEntityID]
  3.     , b.[FirstName]
  4.     , b.[LastName]
  5. FROM [HumanResources].[Employee] a
  6.     INNER JOIN [Person].[Person] b
  7.     ON b.[BusinessEntityID] = a.[BusinessEntityID]

Nothing - except for my poor choice of using meaningless single characters as table aliases. Although it's not a big deal with simpler queries like I've here, it can be a maintenance nightmare with complex queries that join multiple tables.

What about now? Is there anything wrong still?

  1. SELECT
  2.     e.[BusinessEntityID]
  3.     , p.[FirstName]
  4.     , p.[LastName]
  5. FROM [HumanResources].[Employee] e
  6.     INNER JOIN [Person].[Person] p
  7.     ON e.[BusinessEntityID] = p.[BusinessEntityID]

No. This time I use e and p as aliases for Employee and Person respectively. Smart choice!

But I notice a problem in team environments. Different developers use different aliases for the same table resulting in confusion and inconsistency.

For example, some other developer might choose emp and ps instead of e and p like below.

  1. SELECT
  2.     emp.[BusinessEntityID]
  3.     , ps.[FirstName]
  4.     , ps.[LastName]
  5. FROM [HumanResources].[Employee] emp
  6.     INNER JOIN [Person].[Person] ps
  7.     ON emp.[BusinessEntityID] = ps.[BusinessEntityID]

Solution:

I use extended properties - following is an example script.

  1. EXEC sys.sp_addextendedproperty
  2. @name = N'TableAlias',
  3. @value = N'emp',
  4. @level0type = N'SCHEMA', @level0name = HumanResources,
  5. @level1type = N'TABLE',  @level1name = Employee ;
  6. GO
  7.  
  8. EXEC sys.sp_addextendedproperty
  9. @name = N'TableAlias',
  10. @value = N'per',
  11. @level0type = N'SCHEMA', @level0name = Person,
  12. @level1type = N'TABLE',  @level1name = Person ;
  13. GO

Make no mistake, developers are still free to use different aliases, but it is at least easy to quickly see the standard alias by executing either of the following queries.

  1. SELECT [Schema] = s.NAME
  2.     , [Table] = t.NAME
  3.     , [Alias] = ep.value
  4. FROM sys.tables t
  5. INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
  6. LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.object_id
  7.     AND ep.NAME = 'TableAlias' ;
  8.  
  9. SELECT *
  10. FROM fn_listextendedproperty('TableAlias', 'schema', 'Person', 'table', 'Address', NULL, NULL)

Now I've to give a shout out to RedGate's SQL Promt. In addition to other features, SQL Prompt allows you to automatically assign table aliases, and specify custom aliases forcing you to use standard aliases.

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...