Dot Net Interview Cracker - A comprehensive list of Dot Net Interview Questions

Predict the output of these queries

-- Create FirstTable

CREATE TABLE FirstTable (in_id INT, vc_value VARCHAR(10), in_flag INT)

-- Populate FirstTable

INSERT INTO FirstTable (in_id, vc_value, in_flag)

SELECT 1, 'First', 1

UNION ALL

SELECT 2, 'Second', 1

UNION ALL

SELECT 3, 'Third', 2

UNION ALL

SELECT 4, 'Fourth', 1

UNION ALL

SELECT 5, 'Fifth', 2

UNION ALL

SELECT 6, 'Sixth', 1

UNION ALL

SELECT 7, 'Seventh', 2

-- Create SecondTable

CREATE TABLE SecondTable (in_id INT, vc_value VARCHAR(10), in_flag INT)

-- Populate SecondTable

INSERT INTO SecondTable (in_id, vc_value, in_flag)

SELECT 1, 'First', 1

UNION ALL

SELECT 2, 'Second', 1

UNION ALL

SELECT 3, 'Third', 2

UNION ALL

SELECT 8, 'Eighth', 1

UNION ALL

SELECT 9, 'Ninth', 2

Predict the output of these queries?

--Query 1

SELECT * FROM FirstTable a

LEFT JOIN SecondTable b ON a.in_id = b.in_id AND a.in_flag = 1

--Query 2

SELECT * FROM FirstTable a

LEFT JOIN SecondTable b ON a.in_id = b.in_id

WHERE b.in_flag = 1

DROP TABLE FirstTable

DROP TABLE SecondTable

Answer: Query 1 returns the rows of SecondTable where in_flag = 1 with all rows of FirstTable. Query 2 only returns rows from FirstTable and SecondTable where in_flag = 1.

Explanation: The ON clause applies before JOIN so it retrieves all the rows of SecondTable where in_flag = 1 but it does not affect FirstTable so it retrieves all the rows of FirstTable. When WHERE clause is applied, it applies to the complete result so it removes all the rows from FirstTable and SecondTable where Flag is not equal to 1, essentially keeping in_flag = 1 rows from FirstTable and SecondTable.

Predict the output

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.PredictOutput

(in_pk int NOT NULL,

in_pk_value int NOT NULL,

PRIMARY KEY (in_pk)

);

CREATE TRIGGER TestTrig

ON dbo.PredictOutput

FOR INSERT

IF (SELECT COUNT(*) FROM INSERTED WHERE in_pk_value < 0) = 1

BEGIN;

RAISERROR ('Negative values are not allowed!', 16, 1);

ROLLBACK TRAN;

END;

-- Insert attempt #1

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (1, 1);

-- Insert attempt #2

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (2, -2);

-- Insert attempt #3

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (3, -3), (4, -4);

-- Predict the output of this query:

SELECT COUNT(*) FROM dbo.PredictOutput;

DROP TRIGGER TestTrig;

DROP TABLE dbo.PredictOutput;

Answer: 3

Explanation: An insert trigger fires once per execution of a statement that may cause rows to be inserted. During that single execution, all inserted rows will be in the INSERTED pseudo-table (also called magic table). The trigger here works correctly only if a single row is inserted.

Coding triggers that works correctly only if a single row is inserted is bad practice. Even if the application currently inserts rows one at a time, it may change in the future.

In the case of this question, insert attempt #1 succeeds (as there are no rows inserted with in_pk_value below zero).

Insert attempt #2 fails (the negative value in in_pk_value causes an error message and a rollback). This aborts the batch, but the next batch (starting at the next "go") will execute normally.

Insert attempt #3 then succeeds. Both inserted rows violate the business rule, but the trigger here only causes an error message if the number of violations is exactly one.

The correct way to code this would have been to use IF EXISTS (SELECT * FROM INSERTED WHERE in_pk_value < 0). Incidentally, this would also have been more efficient.