Alias Names in Sql Server

Introduction

Here is some of different ways to provide alias names to columns in Sql Server

Giving alias after Derived table name

This is a weird approach most of the people don't know this even exist.

CREATE TABLE AliasNameDemo(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) 

INSERT INTO AliasNameDemo
VALUES      (1,'MyFirstName','MyLastName') 

SELECT * 
FROM   (SELECT firstname + ' ' + lastname 
        FROM   AliasNameDemo) a (fullname) 

Using =

This is my preferred approach. Nothing related to performance just a personal choice. It makes the code to look clean. You can see the resulting column names easily instead of scrolling the code if you have a big expression.

CREATE TABLE AliasNameDemo (id INT,firstname VARCHAR(20),lastname VARCHAR(20)) 

INSERT INTO AliasNameDemo
VALUES      (1,'MyFirstName','MyLastName') 

SELECT FullName = FirstName +' '+ LastName
FROM   AliasNameDemo

Using AS

This is ANSI SQL method works in all the RDBMS. Widely used approach.

CREATE TABLE AliasNameDemo (id INT,firstname VARCHAR(20),lastname VARCHAR(20)) 

INSERT INTO AliasNameDemo
VALUES      (1,'MyFirstName','MyLastName') 

SELECT FirstName +' '+ LastName As FullName
FROM   AliasNameDemo

Without using AS

This syntax will be similar to using AS keyword. Just we don't have to use AS keyword

CREATE TABLE AliasNameDemo (id INT,firstname VARCHAR(20),lastname VARCHAR(20)) 

INSERT INTO AliasNameDemo
VALUES      (1,'MyFirstName','MyLastName') 

SELECT FirstName +' '+ LastName FullName
FROM   AliasNameDemo


2017-07-16
2017-07-16
Microsoft SQL Server Pedia
Icon