Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

sddsfsf

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.

cta14 icon

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

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

2 days 14 Dec 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

8 days 20 Dec 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

2 days 14 Dec 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

2 days 14 Dec 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

1 day 13 Dec 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

2 days 14 Dec 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

5 days 17 Dec 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

8 days 20 Dec 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

9 days 21 Dec 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

2 days 14 Dec 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

15 days 27 Dec 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

8 days 20 Dec 2024