What’s wrong with the following code?

T-SQL
1
2
3
4
5
6
7
SELECT 
    a.[BusinessEntityID]
    , b.[FirstName]
    , b.[LastName] 
FROM [HumanResources].[Employee] a
    INNER JOIN [Person].[Person] b
    ON b.[BusinessEntityID] = a.[BusinessEntityID]
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?

T-SQL
1
2
3
4
5
6
7
SELECT 
    e.[BusinessEntityID]
    , p.[FirstName]
    , p.[LastName] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
    ON e.[BusinessEntityID] = p.[BusinessEntityID]
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.

T-SQL
1
2
3
4
5
6
7
SELECT 
    emp.[BusinessEntityID]
    , ps.[FirstName]
    , ps.[LastName] 
FROM [HumanResources].[Employee] emp
    INNER JOIN [Person].[Person] ps
    ON emp.[BusinessEntityID] = ps.[BusinessEntityID]
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.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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
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.

T-SQL
1
2
3
4
5
6
7
8
9
10
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)
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.