Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
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.
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.
The syntax for Column Alias in SQL Server is as follows
SELECT column_name AS alias_name FROM table_name;
The syntax for Table Alias in SQL Server is as follows
SELECT column_name(s) FROM table_name AS alias_name;
Following are the features of SQL Server Alias
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.
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;
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;
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.
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.
Aliases can be useful when:
Some of the main benefits of SQL aliases include:
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;
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
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment