SQL SERVER – Email from Blog Reader – Not a Potential Bug in SQL – Puzzle

Few days ago, I received wonderful email from blog reader and it was like good puzzle. I enjoyed solving this puzzle. I did not write the name of the blog reader because I am not sure if he wants his name here or not. Please read this article and run the script described in email. You can download the SQL Script here. Also can any of you help this reader why SQL Server is behaving like this. I have already replied him with correct answer where I suggest that it is not bug and have explained him the reason for the same.

Email Details are like this:

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

To prove my point I have attached a SQL Script with this mail. 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 tblENQUIRY

CREATE TABLE tblENQUIRY
(
ENQUIRYID INT PRIMARY KEY,
ENQUIRYNAME VARCHAR(50)
)

Create another table named tblAPPLICANTS

CREATE TABLE tblAPPLICANTS
(
APPLICANTID INT PRIMARY KEY,
ENQUIRYID INT FOREIGN KEY REFERENCES tblENQUIRY(ENQUIRYID),
ISCOMPLETED BIT
)

Insert 5 rows in tblENQUIRY

INSERT INTO tblENQUIRY VALUES (1,'Enquiry1')
INSERT INTO tblENQUIRY VALUES (2,'Enquiry2')
INSERT INTO tblENQUIRY VALUES (3,'Enquiry3')
INSERT INTO tblENQUIRY VALUES (4,'Enquiry4')
INSERT INTO tblENQUIRY VALUES (5,'Enquiry5')

Insert 10 rows in tblAPPLICANTS

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

Select statement to confirm the data is inserted or not.

SELECT *
FROM tblENQUIRY
SELECT *
FROM tblAPPLICANTS

Here is a select query which is not correct. The query tells select APPLICANTID from tblENQUIRY table where ENQUIRYID = 1. But the problem here is tblENQUIRY is not having a column named APPLICANTID. So this query throws an error and that’s a correct behaviour.

SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 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 tblAPPLICANTS SET ISCOMPLETED = 1
WHERE APPLICANTID IN (
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)

Oops, 10 rows affected. Surpised. All the data in ISCOMPLETED field is set to 1 but my inner query
(
SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)

is wrong.

This is just a sample query but When I executed somewhat similar statement 3364 rows in my table was updated.

SELECT *
FROM tblENQUIRY
SELECT *
FROM tblAPPLICANTS

Fortunately It was not on the Live server otherwise things could have been worse for me.

I think this is a severe bug.You all are expert. Can anyone explain me why this is happening?

If I am correct, Please pass on to the Microsoft SQL SERVER Team.

Waiting for a favorable reply.

Thanks,

(Name of blog reader)

If you have directly jumped here without reading complete email I suggest that you read this email again and run the script associated with it and observe why more than one rows are updated.

It is correct observation of reader that when

SELECT APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1

is ran it gives error but when complete query is ran it runs fine because APPLICANTID of sub query is actually column of outer query. Effect query is actually following query and that is why it goes not give any error.

UPDATE tblAPPLICANTS SET ISCOMPLETED = 1
WHERE APPLICANTID IN (
SELECT tblAPPLICANTS.APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1)

Now because same column is used in outerquery and subquery it gives effective result like this.

UPDATE tblAPPLICANTS SET ISCOMPLETED = 1
WHERE 1 IN (1)

Due to same reason all the columns of the table are updated not only one.

You can verify that is same in subquery and outerquery running following query.

SELECT tblAPPLICANTS.APPLICANTID, t.APPLICANTID
FROM tblAPPLICANTS
INNER JOIN (
SELECT tblAPPLICANTS.APPLICANTID
FROM tblENQUIRY
WHERE ENQUIRYID = 1) t ON tblAPPLICANTS.APPLICANTID = t.APPLICANTID

When you run above query you will find that both the APPLICANTID are the same.

I hope this clears the issue. If you have read till this point in this article, congratulations and leave a comment for me that you read. This way I can understand that my readers are interested in topics like this and in future I may have more articles like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Example:
----Example of Correlated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

Example:
----Example of Noncorrelated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE c.Title = 'Mr.'
)
GO

Both of above subqueries can be written using Joins, Exists, In clauses. However, I have tried to demonstrate two different kind of subquery examples. Let me know if you have any questions or wants me to elaborate on this subject.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn’t support it.
USE PUBS
GO
SELECT AVG(COUNT(royalty)) RoyaltyAvg
FROM dbo.roysched
GO

You can get around this problem by breaking out the computation of the average in derived tables.
USE PUBS
GO
SELECT AVG(t.RoyaltyCounts)
FROM
(
SELECT COUNT(royalty) AS RoyaltyCounts
FROM dbo.roysched
) T
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL.

SQL Server Interview Questions and Answers – Part 3

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

UPDATE : Interview Questions and Answers are now updated with SQL Server 2008 Questions and its answers. New Location : SQL Server 2008 Interview Questions and Answers.

What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.

Complete Series of SQL Server Interview Questions and Answers
SQL Server Interview Questions and Answers -Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)