External Column Aliasing in SQL Server 2008.

Though we found inline column aliasing more practical than this new feature, still SQL 2008 provide a way out where you can alias your column externally.

Let’s see how to implement external aliasing, for demonstration I am taking two tables in action.

Creating Table Dept and Emp

CREATE TABLE [dbo].[dept](

      [dept_id] [int] IDENTITY(1,1) primary key,

      [dname] [varchar](10) NULL,

)

CREATE TABLE [dbo].[emp](

      [emp_id] [int] IDENTITY(1,1) NOT NULL,

      [ename] [varchar](10) NULL,

      [dept_id] [int] NULL

)

   

We have inserted few records in both the table

select * from dept

dept_id     dname

----------- ----------

1           software

2           HR

3           sales

(3 row(s) affected)

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           sapna      2

5           sneha      3

6           Kavita     3

7           Binny      1

(7 row(s) affected)

Now we will be querying the table to find the number of employee in each department and also will demonstrate the use of External Aliasing in it,

Query without External Aliasing (In line Aliasing)

select DepartmentID, DepartmentName,TotalEmployee from

(

select d.dept_id as DepartmentID,d.dname DepartmentName, COUNT(e.emp_id) TotalEmployee from dept d,emp e where d.dept_id=e.dept_id group by d.dept_id,d.dname

) as d

 Query with External Aliasing

select DepartmentID, DepartmentName,TotalEmployee from

(

select d.dept_id,d.dname, COUNT(e.emp_id) from dept d,emp e where d.dept_id=e.dept_id group by d.dept_id,d.dname

) as d(DepartmentID, DepartmentName,TotalEmployee)

Both will produce the same result

DepartmentID DepartmentName TotalEmployee

------------ -------------- -------------

1            software       3

2            HR             2

3            sales          2

(3 row(s) affected)

I don’t find any upper edge of this external aliasing but knowing this is interesting for discussion among your friends and showing a higher level of mastery in SQL Server. :P :D

Programmer doesn’t always roar, sometime it quiet to give a chance to his code to roar.   

Post Reference: Vikram Aristocratic Elfin Share