How To Use SQL Aliases in SQL Server

In the office, we've all encountered this scenario. Every team member must have a name that is either too long or hard to say. Most of the time, we call that person by his shorter name and address him that way. Only official work and office documents use his real name.

Similar circumstances exist for tables and databases. Long table or database names can sometimes be problematic when writing a lengthy, complex query. So that we can deal with database queries, we give them a shorter name. These are abbreviations for tables and columns. In the following paragraphs, we will learn about SQL Allias and its table and column aliases.

What is MS SQL Alias

With the help of SQL aliases, a table or a column in a table can be given a temporary name. Aliases can frequently make column names easier to read. A query is the only time an alias is active. An alias can be made by using the AS keyword.

Column Alias in SQL

The syntax for Column Alias in SQL Server is as follows

SELECT column_name AS alias_name
FROM table_name;

Table Alias in SQL

The syntax for Table Alias in SQL Server is as follows

SELECT column_name(s)
FROM table_name AS alias_name;

Features of SQL Alias

Following are the features of SQL Server Alias

• With the help of SQL aliases, a table or a column in a table can be given a temporary name.
• The use of aliases to make column names easier to read is common.
• An alias is only valid for the duration of the query.
• The AS keyword is used to create an alias.

SQL Table and Column Alias with Examples

Let us first create a demo database with tables like Customers and Orders.

The syntax to create the database is

Create a database demo database

The syntax for creating Customers and Orders tables are

create table Customers
 (
  CustomerId int,
  Customername varchar(100),
  Contactname varchar(100),
  Address varchar(100),
  City varchar(100),
  Postalcode int,
  Country varchar(100)
  )

And

Create Table Orders

 (
  Ordered int,
  CustomerId int,
  Employee int,
  Orderdate date,
  ShipperId int
  )

Let us now fill it with data. After data insertion, the Customer table looks like the one below.

And Orders table looks like the one below.

A comprehensive SQL server tutorial guide for beginners & experienced will help you understand what an SQL server is, basic SQL commands, and SQL server career path.

Example of Column Alias in SQL Server

The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Example of Table Alias in SQL Server

The SQL statement that follows chooses all orders from the customer with CustomerID=4 (Around the Horn) as its input. We make use of the "Customers" and "Orders" tables and assign them the table aliases "c" and "o," respectively (in this case, we use aliases to shorten the SQL):

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

What is SQL Server Database Allies

Aliases are usable by any client application. Your client computer can associate with multiple servers using various organization conventions without indicating each server's convention and association subtleties by creating server false names. You can still enable certain network protocols all the time, even if you only use them occasionally. If the SQL Server Browser service has been disabled and the server is set to listen on a non-default port number or named pipe, create an alias specifying the new or named pipe.

Step by Step to Create SQL Server Database Allies using Configuration Manager

1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and select New Alias.

2. In the Alias Name box, type the alias's name. Client applications use this name when they connect.

3. In the Server box, type a server's name or IP address. For a named instance, append the instance name.

4. Select the protocol used for this alias in the Protocol box. Selecting a protocol changes the title of the optional properties box to Port No, Pipe Name, or Connection String.

To Delete an Alias

1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration and select Aliases.

2. In the details pane, right-click the alias that you want to delete, and then select Delete.

You can learn more about how to create or delete a server alias for use by a client by clicking here. 

Uses of MS SQL Server Alias

Aliases can be useful when:

• There is more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined

Benefits of SQL Alias

Some of the main benefits of SQL aliases include:

• It can be used as an obfuscation technique to protect the names of the underlying columns of a query. 
• Allows you to provide more readable names for the column headers when presented in the results. 
• Allows client applications to refer to a calculated field by name when no column name exists.
•  It allows you to reduce the code in your queries and make them more concise.

Drawbacks of SQL Alias

Following are the drawbacks of SQL Server Alias

Their scope is the primary restriction. Aliases give a table or column a new name visible to the outside world.SQL Statements are hierarchical, although this may not be immediately apparent. In the SELECT portion of a query, also known as the output column list, you can only refer to tables or other objects included in the underlying FROM-Clause. Any aliases you define in the FROM clause will also be available elsewhere in the statement because everything in it is based on it.Only people outside of that level of the SQL Statement can view an alias defined in the Select list of a query. The query will not contain any of the aliases you define in the Select list, which is the outermost part of your Select statement.

Let's revisit the straightforward calculation illustration:

FROM borders, SELECT ItemPrice * Quantity AS ExtendedPrice;

Any client code running that query can view the Alias ExtendedPrice outside of that statement. The statement will not contain any mention of it. It cannot be referred to in subsequent estimations, the WHERE condition, or the Request BY statement.

This Will Not Work:

SELECT ItemPrice * Quantity AS ExtendedPrice,
ExtendedPrice * TaxFactor AS TotalPrice
FROM borders
ORDER BY ExtendedPrice DESC;

Despite the inconvenience, you will need to repeat the original expression in both places to achieve the results stated above.

This works:

SELECT ItemPrice * Quantity         AS ExtendedPrice,
  (ItemPrice * Quantity) * TaxFactor AS TotalPrice
FROM borders
ORDER BY (ItemPrice * Quantity) DESC;

Conclusion

In the above writeup, we learned about Aliases. We learned about the definition of SQL Server table and column alias. We also learned about SQL Server Database alias. We also learned about its uses, advantages, and disadvantages. Hope this write-up gives the reader a basic idea about aliases and encourages them to learn more about them. Or, to learn more, you can enroll in our online SQL server training course and shape your ever-growing SQL career.

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now