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
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 |
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 |
Answer / guest
Constraint is better in terms of performance for same
operation. Why??
Is This Answer Correct ? | 0 Yes | 3 No |
Explain what are the restrictions that views have to follow? : SQL Server Architecture
What do you mean by SQL injection attack?
How will you make an attribute not process? : sql server analysis services, ssas
What is the maximum number of instances in 32 bit and 64 bit sql server 2012?
how do you test proper tcp/ip configuration windows machine? : Sql server database administration
What is b tree index?
What is a materialized view?
How many index can be created for single table
4 Answers CarrizalSoft Technologies, Verizon,
whats the maximum size of view state??
What are the options which must be set to allow the usage of optimistic models?
What is service broker? : sql server database administration
What are the new features in SQL Server 2005 when compared to SQL Server 2000?