Why the Aliases 4

[simian336]

Hey Guys,

I was look at someone else's code and I don't understand why they would alias each field with [Extent1] when they aliased the table.

Or why would they add an AS on each field with the same name.

Any thoughts?

Thanks Simi

SELECT 
[Extent1].[Premise_Equipment_ID] AS [Premise_Equipment_ID], 
[Extent1].[Premise_Equipment_Type_ID] AS [Premise_Equipment_Type_ID], 
[Extent1].[Serial_Number] AS [Serial_Number], 
[Extent1].[MAC_ID] AS [MAC_ID], 
[Extent1].[IP_Address] AS [IP_Address], 
[Extent1].[Date_Installed] AS [Date_Installed], 
[Extent1].[Last_Inventory_Date] AS [Last_Inventory_Date], 
[Extent1].[Location_At_Premise] AS [Location_At_Premise], 
[Extent1].[Equipment_Status_ID] AS [Equipment_Status_ID], 
[Extent1].[Additional_Equipment_Notes] AS [Additional_Equipment_Notes], 
[Extent1].[Adtran_Information] AS [Adtran_Information], 
[Extent1].[Bridging_Instructions] AS [Bridging_Instructions], 
[Extent1].[Power_Supply_Information] AS [Power_Supply_Information], 
[Extent1].[Changed_Date] AS [Changed_Date], 
[Extent1].[Changed_User] AS [Changed_User]
FROM [dbo].[Premise_Equipment] AS [Extent1]
WHERE [Extent1].[Premise_Equipment_Type_ID] = @EntityKeyValue1

 

[DaveInIowa]

Typically when you see ugly code like that, it means it was created by a code generator such as Query by Example.

 

[SgtJarrow]

Dave is right..probably built by a code generator.

One comment though...The table alias on each column.
My company's code standard is to ALWAYS alias a table AND to ALWAYS include the table alias in each column definition.
Why when there is only one table in the query?
Because tomorrow you will have to add one or more tables to the query.

Column aliases are used if/when needed/appropriate under our standards...

And it makes maintaining the code easier long term...IMHO.

SELECT
    ta.Id 'EmployeeId', --Alias needed for clarity
    ta.FirstName, --No alias needed, column name is self-documenting
    ta.LastName, --No alias needed, column name is self-documenting
    ta.FirstName + ' ' + ta.LastName 'FullName' --Alias needed for clarity
FROM TableA ta;

Easily becomes

SELECT
    ta.Id 'EmployeeId', --Alias needed for clarity
    ta.FirstName, --No alias needed, column name is self-documenting
    ta.LastName, --No alias needed, column name is self-documenting
    ta.FirstName + ' ' + ta.LastName 'FullName' --Alias needed for clarity
    tb.Name 'DepartmentName' --Alias used for clarity
FROM TableA ta
INNER JOIN TableB tb
    ON ta.DeptId = tb.Id;

Without the TableA alias in the first example, I would need to modify the entire query before adding in the TableB JOIN. Or at least I would need to modify ANY column that is named the same in the two tables and might create a processing error.

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[dhookom]

Star for SgtJarrow. Some great "standards" for writing SQL that is clear and sustainable.

My personal naming convention doesn't allow field names like "Id" and "Name". Also, every field in my databases has a unique name but I don't want to start a naming conventions war ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SgtJarrow]

We could argue all day on standards and naming conventions. Everyone has personal tastes and preferences.

The IMPORTANT things about standards are:
[ul]
[li]You have some standards[/li]
[li]They make some sense[/li]
[li]You ADHERE to them[/li]
[/ul]

For the most part, I (read as you) can adapt to any standard or convention. But you have to stick with it and use it correctly to reap the greatest benefits.

Robert "Wizard" Johnson III
U.S. Military Vets MC
Data Integration Engineer

 

[simian336]

Thanks all for the comments. I am think that Dave is correct.

I have seen a couple of other queries where the apparent same author aliased the table with [Extent1]. I thought it was a quirk of the author but it makes since that it would be a code generator.

Thanks

Simi