Assert operator SQL Server – Part1

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

AssertGraohicalPlan

AssertToolTip

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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. Required fields are marked *