XML FeedsWhat'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.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.