Skip to main content

Interview Question and answer - SQL Server Day 1

Question: What is a Stored Procedure?

Answer: Stored Procedure is the collection of Structured Query Language (SQL) statements with an assigned name which stored in the database in compiled form. Stored Procedure can be used over the network by several clients using different input data because the stored procedure can accept input parameters. Stored procedure improved performance reducing network traffic.

Question: What is Trigger in SQL Server?

Answer: Trigger is the same as a procedure which is executed on the action when an event (INSERT, DELETE, UPDATE etc) occurs. DBMS manage and store to trigger. Triggers are commonly used to perform auditing action, to maintain the table integrity in place of native constraints such as foreign key and check constraints, and perform other DML processing. When a trigger is at work, the process can’t be completed until trigger completion because the trigger operates under the scope of a transaction.

Question: Type of trigger?

Answer: There are two types of trigger DML and DDL.
1-  DML Trigger: DML trigger is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. There are two types of DML trigger.

A)  Instead of Trigger: Instead of trigger override the standards actions of the triggering, means fire in the place of the triggering action such as insert, delete or update. For example:  when the value is updated in an hourly wage column in a payroll table exceeds a specific value.

B)  After Trigger: This trigger fire after execution of action query. After trigger fire for both DML and DDL statements.


2-  DDL Trigger: This type of trigger fired when an action occurs like Drop, Create, and Alter Table DDL triggers are always after a trigger.