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.
