SQL PUZZLE | THE IDENTITY & CONSTRAINT Puzzle – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL query and provide us the output without really executing them at the SSMS. Here you need to provide the data we have in the TestIdentityWithConstraint table.

Q1. What will be the OUTPUT of the following statement?

--

CREATE TABLE TestIdentityWithConstraint
(
	 Id INT IDENTITY(1,1)
	,Vals INT 
)
GO

ALTER TABLE TestIdentityWithConstraint ADD CONSTRAINT ck_Vals CHECK (Vals >= 10)
GO

INSERT INTO TestIdentityWithConstraint(Vals) VALUES (9)
GO

INSERT INTO TestIdentityWithConstraint(Vals) VALUES (11)
GO

SELECT * FROM TestIdentityWithConstraint

--

SOLUTION

Reason – So clearly the first insert will be blocked by the constraint as the vals column cannot accept value below 11. But the Identity column till then created the identity for the column(Id). So when we insert the second row which was successfully inserted will have identity 2 as identity was already 1. So the values will be 2, 11. Please try and let me know if you see any difference.

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com