Which is better in performance - CONSTRAINT or TRIGGER over
a column which restricts say an input of particular value
in a column of a table?




Answers were Sorted based on User's Feedback



Which is better in performance - CONSTRAINT or TRIGGER over a column which restricts say an input ..

Answer / jerry joseph

Restricting the input using CONSTRAINT will have better
performance but there is a limit to the types of constraints
which we can provide..

With TRIGGERS you have more flexibility and provide a more
variety restrictions to the input.. for example you can
prevent a normal delete and make it a soft delete by setting
an IsDeleted flag using trigger.. we cant have that kind of
flexibility using Constraints..

Is This Answer Correct ?    6 Yes 1 No

Which is better in performance - CONSTRAINT or TRIGGER over a column which restricts say an input ..

Answer / monal

Constraints gets evaluated before statement can be either
inserted or deleted or updated. While trigger executes
after or at the same time statement is executing. So if you
have to roll back transactio because transaction not
meeting condition there is no overhead when you are using
constraint ,but if you have to roll back transaction and
you have a trigger on that table or column either 1
transaction goes through before rolling back. So basically
trigger consumes more resources.

Is This Answer Correct ?    7 Yes 3 No

Which is better in performance - CONSTRAINT or TRIGGER over a column which restricts say an input ..

Answer / guest

Constraint is better in terms of performance for same
operation. Why??

Is This Answer Correct ?    0 Yes 3 No



Post New Answer



More SQL Server Interview Questions

Differencr Between DELETE and TRUNCATE?

3 Answers   ADP,


What is the maximum size of a row in sql server?

0 Answers  


Why would you use sql agent?

0 Answers  


How much space does sql server 2016 take?

0 Answers  


What do you understand by user-defined function in the sql server?

0 Answers  






What is scan table/view and seek table/view when its occurs? : sql server database administration

0 Answers  


What is always encrypted?

0 Answers  


How to convert numeric expression data types using the cast() function?

0 Answers  


How many files can a database contain in sql server?how many types of data files exists in sql server? How many of those files can exist for a single database?

0 Answers  


Difference Between varchar and nvarchar datatype?

3 Answers   Satyam,


What do you mean by subquery?

0 Answers  


Why do we need different type of parameter?

0 Answers  






Categories