Stored Procedures and Triggers Interview Questions
29. What is a stored procedure?
Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.
Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.
31. What is a trigger?
Database triggers are sets of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.
32. Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed, Commit and Rollback can be performed to retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot be performed. Where condition can be used along with delete statement but it can't be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a table.
33. What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.
34. What is Union, minus and Interact commands?
MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.