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

Misbehaving Subqueries!!! What I Do?

Misbehaving Subqueries!!!  Then why it is not seize up by compiler??? Then what should I do?

There are many tricky programming error involving subqueries  which even compiler parse it correctly. We will be describing those bugs then make the recommendation, what we can do to avoid such bugs.

Let’s take an example to see where we are making mistakes in subquery.

Here in this example, I have taken two table Department and Employee.

create table Department

(dept_id int identity(1,1) primary key,

dept_name varchar(10))

create table Employee

(emp_id int identity(1,1),

emp_name varchar(10),

designation varchar(10),

d_id int references Department(dept_id) )

The dept_id in Department table is primary key, and d_id in Employee table is corresponding reference key to the dept_id of Department table.

Now I am inserting few data to both the tables

insert into Department values('HR')

insert into Department values('Operation')

insert into Department values('Marketing')

insert into Department values('Software')

insert into Employee values('Abhilash','HR Executive',1)

insert into Employee values('Vidisha ','Marketing Manager',2)

insert into Employee values('Vibhuti  ','Operation Incharge',3)

Here you can see there is no employee belonging to the Software Department.

Now suppose you are asked to return the Department name where there are no employee belonging to it, you will write a subquery to return the desired result like this below:

select dept_id, dept_name from Department

where dept_id not in (select dept_id from Employee)

The above query is supposed to return the desired result that is Software department. But to your surprise, this query returns an empty set. Can you tell why? Can you identify bug in the code?

Well, the matter is, the reference column in Employee table referencing to dept_id of Department table is not dept_id instead it is defined as d_id.

Realizing this you did probably expect the query to have failed because of invalid column name, sure enough if you run that part of subquery it will fail like this:

select dept_id from Employee

Msg 207, Level 16, State 1, Line 1

Invalid column name 'dept_id'.

However in the context of outer query, apparently the subquery is valid, though we don’t get desired result!!!

select dept_id, dept_name from Department

where dept_id not in (select dept_id from Employee)

dept_id     dept_name

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

(0 row(s) affected)

Why So?? The name resolution process works from inner nesting level outward. The query processor first looked for dept_id in Employee Table, not having found such column name, it looked for one in Department table ( the outer level) and found it. It is interpreted as

select dept_id, dept_name from Department d

where dept_id not in (select d.dept_id from Employee e)

Logically, the query doesn’t make any sense.

To fix the problem, of course, you need to use the correct column name from Employee table.

select dept_id, dept_name from Department

where dept_id not in (select d_id from Employee)

dept_id     dept_name

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

4           Software

(1 row(s) affected)

Recommendation!!! To avoid such bugs in future, it is good practice to always include the table name or table alias for all attribute in subquery. Like this

select d.dept_id, d.dept_name from Department d

where d.dept_id not in (select e.d_id from Employee e)

Get pleasure from coding

Post Reference: Vikram Aristocratic Elfin Share