SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Standardize Table Aliases

What’s wrong with the following code?

    , b.[FirstName]
    , b.[LastName] 
FROM [HumanResources].[Employee] a
	INNER JOIN [Person].[Person] b
	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?

    , p.[FirstName]
    , p.[LastName] 
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	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.

    , ps.[FirstName]
    , ps.[LastName] 
FROM [HumanResources].[Employee] emp
	INNER JOIN [Person].[Person] ps
	ON emp.[BusinessEntityID] = ps.[BusinessEntityID]


I use extended properties – following is an example script.

EXEC sys.sp_addextendedproperty
@name = N'TableAlias', 
@value = N'emp', 
@level0type = N'SCHEMA', @level0name = HumanResources, 
@level1type = N'TABLE',  @level1name = Employee ;

EXEC sys.sp_addextendedproperty 
@name = N'TableAlias', 
@value = N'per', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Person ;

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.

SELECT [Schema] = s.NAME
	, [Table] = t.NAME
	, [Alias] = ep.value
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.object_id
	AND ep.NAME = 'TableAlias' ;

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.

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.


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

Loading comments...