SQL Interview Question with Answers

SQL Interview Question with Answers

Previous Questions(31-41)

41. What are the types of UDF?

1. Scalar

Deterministic UDF: UDF in which particular input results in particular output. In other words, the
output depends on the input.
Non-deterministic UDF: UDF in which the output does not directly depend on the input.

2. In-line UDF:

UDFs that do not have any function body(BEGIN...END) and has only a RETURN statement. In-line
UDF must return 2D data.

3. Multi-line or Table Valued Functions:

It is an UDF that has its own function body (BEGIN ... END) and can have multiple SQL

statements that return a single output. Also must return 2D data in the form of table variable.

42. What is the difference between a nested UDF and recursive UDF?

• Nested UDF: calling an UDF within an UDF
• Recursive UDF: calling an UDF within itself

43. What is a Trigger?

• Triggers do not have any parameters or return statement.
• It is a precompiled set of TSQL statements that are automatically executed on a particular DDL, DML or log-on event.
• Triggers are the only way to access to the INSERTED and DELETED tables (aka. Magic Tables). 
• You can DISABLE/ENABLE Triggers instead of DROPPING them:

DISABLE TRIGGER <name> ON <table/view name>/DATABASE/ALL SERVER

ENABLE TRIGGER <name> ON <table/view name>/DATABASE/ALL SERVER

44.What are the types of Triggers?

1. DML Trigger

DML Triggers are invoked when a DML statement such as INSERT, UPDATE, or DELETE occur

which modify data in a specified TABLE or VIEW.

A DML trigger can query other tables and can include complex TSQL statements. They can cascade

changes through related tables in the database.

They provide security against malicious or incorrect DML operations and enforce restrictions that are

more complex than those defined with constraints.

2. DDL Trigger

Pretty much the same as DML Triggers but DDL Triggers are for DDL operations. DDL Triggers are

at the database or server level (or scope).

DDL Trigger only has AFTER. It does not have INSTEAD OF.

3. Logon Trigger

Logon triggers fire in response to a logon event.

This event is raised when a user session is established with an instance of SQL server. Logon

TRIGGER has server scope.

45. What are ‘inserted’ and ‘deleted’ tables (aka. magic tables)?

• They are tables that you can communicate with between the external code and trigger body.
• The structure of inserted and deleted magic tables depends upon the structure of the table in a DML statement. 
• UPDATE is a combination of INSERT and DELETE, so its old record will be in the deleted table and its new record will be stored in the inserted table.

46. What are some String functions to remember? LEN(string): returns the length of string.

UPPER(string) & LOWER(string): returns its upper/lower string

LTRIM(string) & RTRIM(string): remove empty string on either ends of the string LEFT(string):
extracts a certain number of characters from left side of the string RIGHT(string): extracts a certain
number of characters from right side of the string SUBSTRING(string, starting_position, length):
returns the sub string of the string REVERSE(string): returns the reverse string of the string

Concatenation: Just use + sign for it REPLACE(string, string_replaced, string_replace_with)

47. What are the three different types of Error Handling?

1. TRY CATCH

The first error encountered in a TRY block will direct you to its CATCH block ignoring the rest of the

code in the TRY block will generate an error or not.

2. @@error

stores the error code for the last executed SQL statement. If there is no error, then it is equal to 0.

If there is an error, then it has another number (error code).

3. RAISERROR() function

A system defined function that is used to return messages back to applications using the same format

which SQL uses for errors or warning message.

48. Explain about Cursors ..?

• Cursors are a temporary database object which are used to loop through a table on row-by-row
• basis. There are five types of cursors:

1. Static: shows a static view of the data with only the changes done by session which opened the cursor.
2. Dynamic: shows data in its current state as the cursor moves from record-to-record.
3. Forward Only: move only record-by-record
4. Scrolling: moves anywhere.
5. Read Only: prevents data manipulation to cursor data set.

49. What is the difference between Table scan and seek ?

• Scan: going through from the first page to the last page of an offset by offset or row by row. 
• Seek: going to the specific node and fetching the information needed.
• ‘Seek’ is the fastest way to find and fetch the data. So if you see your Execution Plan and if all of them is a seek, that means it’s optimized.

50. Why are the DML operations are slower on Indexes?

• It is because the sorting of indexes and the order of sorting has to be always maintained.
• When inserting or deleting a value that is in the middle of the range of the index, everything has to be rearranged again. It cannot just insert a new value at the end of the index.