Assert operator SQL Server – Part3

Dear Friends,

I hope you liked my previous posts Assert operator SQL Server Part1  and Part2. Today, we are going to see that this operator is used to check sub query too.

Assert operator comes into play in validating the condition that a scalar sub query returns just one value and not anything more than that, for example consider following statement.

INSERT INTO Table1(ID, Selected) VALUES((SELECT ID FROM Table1), 'Y')





Execution plan is broken into two parts in order to fit into the window and to have a better clarity. From graphical plan we can see that two assert operations appearing on the plan but to understand what these expressions are doing internally, let us decode the execution plan in text.


Stream aggregate operator counts how many rows to be returned by the sub query then this value is passed to Assert operator which then checks the validity.

That’s all for today, there is much to come on One operator a day series; see you tomorrow.

Happy learning.



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.