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.
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
