SQL Server Interview Questions and Answers Part 3

16. What is an execution plan?

Answer:

An execution plan is a graphical or textual road map that shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query.

It is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.

17. What Is SQL Profiler?

Answer:

It is a tool that provides a user interface to the SQL Trace utility and enables us to monitor events in the SQL server.

With SQL profiler, you can capture and save data about each event of a file or a table for analysis later.

18. What is COALESCE in SQL Server?

Answer:

SQL Server COALESCE() Function returns the first non-null expression in a list.

Example:
SELECT COALESCE(NULL, NULL, NULL, 'tutorialride.com', NULL, 'careerride.com');

Result:
tutorialride.com

19. How exceptions can be handled in SQL Server Programming?

Answer:

Exceptions are handled in SQL Server Programming using TRY...CATCH statement, just the same as in the programming languages like C++, Java etc.

20. What is the purpose of FLOOR function?

Answer:

SQL Server FLOOR() Function returns the largest integer value that is equal to or less than a number.

Example:
SELECT FLOOR(5.75) AS FloorValue;

Result:
5

21. What is a Trigger? What are the types of Triggers?

Answer:

Trigger is a special kind of stored procedure that fires automatically, get invoked when an event occurs in the database server. We can create Data Manipulation Language (DML) triggers and Data Definition Language (DDL) triggers in SQL Server 2012.

A DML trigger is used for INSERT, DELETE and UPDATE statements of a table or view.

A DDL trigger is executed for CREATE, ALTER and DROP statements of a table or view

There are 3 types of Triggers:

• DDL Trigger
• DML trigger
- AFTER Triggers
- INSTEAD Of Triggers
• Logon triggers

22. Difference between Stored Procedure and trigger

Answer:

Triggers fire implicitly while Stored Procedures fire explicitly.