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

User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

Microsoft best practice says, User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

I have seen many blogs saying it is best practice not to use SP_ prefix to the user defined stored procedure. ???

The question of the hour is what it makes difference from system procedure if we create user defined stored procedure with SP_ prefix, and then we decide whether to stop writing stored procedure with SP_ prefix.

We will go into the depth to find out how it works when we declare stored procedure with SP_ prefix by taking enough of examples.

Example 1

I am creating a stored procedure in tempDatabase, then I am creating a user defined stored procedure with the name sp_tables.

create database tempDatabase

Command(s) completed successfully.

Database is ready.

use tempDatabase

go

CREATE PROCEDURE sp_tables

AS

BEGIN

      select * from EmployeeList

END

GO

Now we have created procedure which returns record set of all employee from EmployeeList table

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables

tempDatabase	dbo	EmployeeList		TABLE	NULL
tempDatabase	INFORMATION_SCHEMA	CHECK_CONSTRAINTS		VIEW	NULL
tempDatabase	INFORMATION_SCHEMA	COLUMN_DOMAIN_USAGE		VIEW	NULL

The output is something different from what we have expected, so where we go wrong???

The finding is, if user defined stored procedure name is same as system defined stored procedure, then the DB engine search the SP from Master database not from the user defined database.

So, the procedure will be executed from "MASTER" database... Not from user database. In our case the stored procedure is System stored procedure name, So, its searched and executed from MASTER database itself.

Example 2

Lets create a stored procedure in TempDatabase with the name sp_Tables1, which is not a system defined stored procedure name.

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

It results as the same, what we expected... How and why?, The stored procedure name was prefixed as 'Sp_' , Then it should be searched in "MASTER" database. correct?

NO The reason is, This is not a "SYSTEM STORED PROCEDURE", So, It will be searched in Local database first and then "MASTER" database, In our case, The procedure will be there in user database (tempDatabase) itself, So, Its searched and executed from User database itself...

Example 3

I am creating one stored procedure in "MASTER" database with name Sp_Tables1. Before that we first need to drop the procedure sp_table1 from user database (tempDatabase) which we had created in previous example.

use tempDatabase

drop procedure sp_tables1

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure. We are executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

Expected output

Don’t Know, because no procedure with sp_tables1 is present in user database(tempDatabase).

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, The procedure is NOT A SYSTEM STORED PROCEDURE..., So, The it searches the Stored procedure in Local database first. If it is not there in local then only it searches in MASTER database.

In our case, the procedure will be there in MASTER database. So, It is searched in use database first, But the procedure will not be there in user database, So, Its searched and executed from MASTER database.

Example 4

Now lets create one stored procedure in both MASTER and User database (tempDatabase) named Sp_Tables1.

IN MASTER DATABASE

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      print 'from master'

END

GO

Command(s) completed successfully.

IN USER DATABASE

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Command(s) completed successfully.

Now its time to execute the stored procedure. We will executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, the procedure is NOT A SYSTEM STORED PROCEDURE..., So, the search operation is performed in user database (TempDatabase) first and then MASTER database.

If Procedure is there in user database then, the procedure will be executed from user database itself, and it will not be search in MASTER database.

Conclusion:

The 'SP_' prefixed stored procedure will not be searched the stored procedure in MASTER database always. It will be searched based on the various scenario described above.

When Object name is SYSTEM DEFINED NAME then only it searches the procedure in MASTER database first and then user current database.

Now it’s on u whether to use SP_ prefix to user defined stored procedure or not…

But DB practice says, not to use SP_ prefix to user procedure.

Coding gives immense pleasure J

Post Reference: Vikram Aristocratic Elfin Share

IDENT_CURRENT function vs @@IDENTITY in SQL Server

IDENT_CURRENT function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Let’s take an example for learning purpose

create table EmpTemp

(id int identity(1,1),

empName varchar(10))

Command(s) completed successfully.

Here ID is an identity column which will gets increment by one with every insert since the seed for the increment is set to 1.

Let’s insert few rows to EmpTemp table. Values for ID column is not required since it is an identity column, so automatically on insertion of any row to the table set the value of ID in corresponding row.

insert into EmpTemp(empName) values ('Aakhya')

insert into EmpTemp(empName) values ('Kruthika')

insert into EmpTemp(empName) values ('Aanandita')

insert into EmpTemp(empName) values ('Chandanika')

insert into EmpTemp(empName) values ('Ceitra')

insert into EmpTemp(empName) values ('Arasi')

Accede to see the output of Select query

select * from EmpTemp

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

(6 row(s) affected)

Now run the below query to see the result of IDENT_CURRENT. The IDENT_CURRENT function takes only Table or View name as parameter.

select IDENT_CURRENT('EmpTemp')

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

6

(1 row(s) affected

Now insert one more row and then check this:

insert into EmpTemp(empName) values ('Siddhiksha')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

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

7

(1 row(s) affected)

What does the function do ?

CONCLUSION: This always gives you the current id value from the table.

PART 2: Interesting to see!!! THE @IDENTITY

Run the query below.

insert into EmpTemp(empName) values ('Sakshi')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

select @@IDENTITY

OUTPUT

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

8

Both IDENT_CURRENT and @@IDENTITY that means @@IDENTITY does the same thing!!!!

Hang around and observe:

Open a new Query window and run the below query again:

select IDENT_CURRENT('EmpTemp')

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

8

(1 row(s) affected)

select @@IDENTITY

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

NULL

(1 row(s) affected)

Weird and wonderful! correct ???

·       IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

Post Reference: Vikram Aristocratic Elfin Share