Weird thing with inner queries in SQL SERVER 2005

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feel that THIS IS A BUG in SQL SERVER which actually can create lots of data discrepancy.

To prove my point I have created a sample SQL Script. I want you to run the script in SQL SERVER.

Details of the Script

· Create new database.

CREATE DATABASE DB_TEST

· Use this Database.

USE DB_TEST

· Create a table named tblCategory

CREATE TABLE tblCategory
(
CategoryID INT PRIMARY KEY,
CategoryNAME VARCHAR(50)
)

· Create another table named tblProduct

CREATE TABLE tblProduct
(
ProductID INT PRIMARY KEY,
CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
ISCOMPLETED BIT
)

· Insert 5 rows in tblCategory

INSERT INTO tblCategory VALUES (1,'Category1')
INSERT INTO tblCategory VALUES (2,'Category2')
INSERT INTO tblCategory VALUES (3,'Category3')
INSERT INTO tblCategory VALUES (4,'Category4')
INSERT INTO tblCategory VALUES (5,'Category5')

· Insert 10 rows in tblProduct

INSERT INTO tblProduct VALUES (1,1,0)
INSERT INTO tblProduct VALUES (2,1,0)
INSERT INTO tblProduct VALUES (3,2,0)
INSERT INTO tblProduct VALUES (4,2,0)
INSERT INTO tblProduct VALUES (5,3,0)
INSERT INTO tblProduct VALUES (6,3,0)
INSERT INTO tblProduct VALUES (7,4,0)
INSERT INTO tblProduct VALUES (8,4,0)
INSERT INTO tblProduct VALUES (9,4,0)
INSERT INTO tblProduct VALUES (10,5,0)

· Select statement to confirm the data is inserted or not.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

· Here is a select query which is not correct. The query tells select ProductID from tblCategory table where CategoryID = 1. But the problem here is tblCategory is not having a column named ProductID. So this query throws an error and that's a correct behaviour.

SELECT ProductID FROM tblCategory WHERE CategoryID = 1

· Here is the magic. I have used the above select query which is not correct as a inner query with a update statement. What to do you think? What will happen? Update query should throw an error. Logically it should.I was thinking the same But just execute this update query and you will be shocked.

UPDATE tblProduct SET IsCompleted = 1 WHERE ProductID IN (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

· Oops, 10 rows affected. Surpised. All the data in IsCompleted field is set to 1 but my inner query (SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

· This is just a sample query but When I executed such a similiar statement, 3364 rows in my table was updated.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

I think this is a bug but actaully it's not. Please read comments for more information on this..

Java script function to get browser name..

We have this requirement in most of the application to know that which browser user is using to view the web site. We can use javascript to know browser name and this function works with all the broswers...

function GetBrowser()
{
           if (/Firefox[\/\s](\d+\.\d+)/.test(navigator.userAgent))
           { 
                 var ffversion=new Number(RegExp.$1) 
                 if (ffversion>=3)
                  alert("FF 3.x or above")
                 else if (ffversion>=2)
                  alert("FF 2.x")
                 else if (ffversion>=1)
                  alert("FF 1.x")
            }
            else if(/Chrome[\/\s](\d+\.\d+)/.test(navigator.userAgent))
            {
                 alert("Chrome.");
            }

           else if(/Opera[\/\s](\d+\.\d+)/.test(navigator.userAgent))
            {
                 alert("Opera");
            }
            else
            {
             alert(navigator.appName);
            }
             
            return false;
}

 window.onload  = GetBrowser;

Enjoy..............

Disabling button at client side before firing server side event

If we want to disable the button when clicked until it's execute the server side code then check out this link....

Enjoy...