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.