An assert operator SQL Server appears in query plan when there is a need to verify a value, commonly constraints like CHECK or FOREIGN KEYs however other scenarios are also possible. It verifies every row to ensure condition is met.
Let’s see where exactly SQL Server uses this operator in practical.
IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
CREATE TABLE Table1(ID Integer, Selected CHAR(1))
ALTER TABLE Table1 ADD CONSTRAINT CK_Selected_Y_N CHECK(Selected IN('Y','N'))
/*Step4 : This step may fail as we are not inserting Y or N and you need to click on
'Display Estimated Execution Plan' icon on tool bar*/
INSERT INTO Table1(ID, Selected) VALUES(1,'Q')
As can be seen, execution plan used assert operator to ascertain that the inserted values doesn’t violate check constraint. If the value is different from either Y or N then return 0 else return NULL. That’s all for today, see you tomorrow with more on assert operator.