Assert operator in SQL Server execution plan

Asset Operator appears in the execution plan to enforce constraint such as uniqueness referential integrity or check constraint. This happen during the execution and validate the constraint and raise the error if it’s violated the constraint.

It generate on below scenario’s 

Ø  Check constraint

Ø  Unique constraint/ primary key constraint

Ø  Foreign key violation

Read: Constraints in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2017/08/constraints-in-sql-server.html

Icon of asset Operator

Let’s see the example

Check constraint demo

CREATE TABLE tbl_assert_operator_demo   (      id       INT PRIMARY KEY,      emp_name VARCHAR(100),      emp_add  VARCHAR(100),      salary   INT CONSTRAINT  check_salary CHECK(salary>0),   );

Now inserting a record and see the execution plan

insert into tbl_assert_operator_demo (id,emp_name,emp_add,salary) values (2,'Bagesh','Noida',1000)

See the Asset Operator in the execution plan. 

Let’s see if in the table we don’t have any constrain then what happen.

CREATE TABLE tbl_assert_operator_demo_without_any_constraint   (      id       INT ,      emp_name VARCHAR(100),      emp_add  VARCHAR(100),      salary   INT ,   );

Inserting the same record in this table.

insert into tbl_assert_operator_demo_without_any_constraint (id,emp_name,emp_add,salary) values (1,'Bagesh','Noida',1000)

See the execution plan.

It is not generating the Asset Operator in this execution plan.

Foreign key constraint demo

Let’s see the foreign key dome.

Creating two table as below.

CREATE TABLE tbl_assert_operator_demo_emp_dept   (      dept_id   INT PRIMARY KEY,      dept_name VARCHAR(100)   );  CREATE TABLE tbl_assert_operator_demo_foreign_emp   (      id       INT PRIMARY KEY,      emp_name VARCHAR(100),      emp_add  VARCHAR(100),      salary   INT,      dept_id INT Foreign Key                  References tbl_assert_operator_demo_emp_dept(dept_id)   );

 Both table created successfully.

Let’s insert records in both table.

insert into tbl_assert_operator_demo_foreign_emp (id,emp_name,emp_add,salary,dept_id) values (1,'Bagesh','Noida',1000,1)

Record inserted see the execution plan