What’s wrong with the following code?

SELECT 
    a.[BusinessEntityID]
    , 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?

SELECT 
    e.[BusinessEntityID]
    , 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.

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

Solution:

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 ;
GO
EXEC sys.sp_addextendedproperty 
@name = N'TableAlias', 
@value = N'per', 
@level0type = N'SCHEMA', @level0name = Person, 
@level1type = N'TABLE',  @level1name = Person ;
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.

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' ;
SELECT *
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.