Hi Geeks,
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.
--Step1
IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
GO
--Step2
CREATE TABLE Table1(ID Integer, Selected CHAR(1))
GO
--Step3
ALTER TABLE Table1 ADD CONSTRAINT CK_Selected_Y_N CHECK(Selected IN('Y','N'))
GO
/*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')
GO
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.
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
