Alias name is a name which is given to a database Table or a column temporarily. Generally we create Alias by using the keyword called ‘AS’.
- We can also create without this keyword as ‘AS’ keyword is optional in SQL Server.
- We can give alias names to Views, User defined functions, Temporary tables, CTEs….etc.
- The scope of the Alias is within the SELECT query.
- Alias plays key role when we are working with Sub queries. It may be optional in few cases. But it is mandatory to give alias to all derived columns in a sub query. Because, SQL Server Engine treats the entire sub query as a table when it is executing the entire query. A table can not exists without its column names. That’s why it throws error when we execute without alias to derived columns.
- If its name contains space in between, we must enclose the full name in Double quotes or square brackets. Otherwise Engine treats only the first word as alias and can not understand other words. So it throws error. Creating with space in between is not a good practice. Try to avoid spaces. Instead you can use underscore(_).
SELECT <Column_Name(s)> FROM <Table_Name> AS Alias_Name;
SELECT <Column_Name(s)> FROM <Table_Name> Alias_Name;
SELECT TOP 2 [EMP].[BusinessEntityID], [EMP].[JobTitle]
FROM [HumanResources].[Employee] AS [EMP];
In the above example, EMP is the Alias name for the table [Employee]. While fetching data from columns, we have to use Alias_Name.Column_Name in the select statement. It might work in few cases without prefixing Alias_Name to column_name. But what if we are joining more than one table and two of the tables have the same column names which we are using in the select statement? The Engine does not know from which table it has to take values for the particular column. So it throws Ambiguity error.
SELECT <Column_Name> AS Alias_Name FROM <Table_Name>;
SELECT <Column_Name> Alias_Name FROM <Table_Name>;
SELECT TOP 2 [BusinessEntityID] AS [BE ID], [JobTitle] [JT]
In the above example, BEID is the Alias name(with AS keyword) for the column [BusinessEntityID] and JT(without AS keyword) is for [JobTitle]. I do not know whether you have noticed it or not. I have given space in Alias name for the [BusinessEntityID] column and enclosed the same in square brackets.
- Alias names do not affect the query performance negatively. We can use as many as we can.