SQL Interview Question with Answers
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:
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:
44.What are the types of Triggers?
1. DML Trigger
2. DDL Trigger
3. Logon Trigger
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
2. @@error
3. RAISERROR() function
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:
- Static: shows a static view of the data with only the changes done by session which opened the cursor.
- Dynamic: shows data in its current state as the cursor moves from record-to-record.
- Forward Only: move only record-by-record
- Scrolling: moves anywhere.
- 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.
No comments:
Write commentsPlease do not enter spam links