Oct 4, 2016

Interview Question and answer - SQL Server Day 1

Question: What is Stored Procedure?

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

Question: What is Trigger in SQL Server?

Answer: Trigger is same like procedure which is executed on an action when 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 ken and check constraints, and perform other DML processing. When trigger is at work, process can’t be completed until trigger completion because trigger operate under scope of transaction.

Question: Type of trigger?

Answer: There are two types of trigger DML and DDL.
1-  DML Trigger: DML triggers 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 being 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 action occurs like Drop, Create, and Alter Table DDL triggers are always after trigger.