SQL Server Interview Questions -- Part 3

21. What is a user defined function (udf) ?
Ans: UDF is very similar to functions created in any programming language but with SQL statements. So, it can be defined as database unit of work created using SQL commands.

22. Compare UDFs and Stored procedure ?
Ans: UDFs can be used in select, where, join or case statements while SPs cannot be. UDFs cannot be used in DML statements to modify, while we can use them in stored procedures.

23. When do you use cursor ?
Ans: If you have to process one row at a time and loop through a set of rows, you can use cursors.

24. What are the special tables that can be used only within trigger ?
Ans: "Inserted" to check the rows inserted through the trigger and "Deleted" to check the rows deleted through the trigger.

TABLEA

ID
1
2
3

TABLEB

ID
1
3
5

25. What is the result of Select * from TABLEA A join TABLEB B on a.ID = b.ID ?
Ans:
1 1
3 3

26. What is the result of Select * from TABLEA A left join TABLEB B on a.ID = b.ID ?
Ans:
1 1
2 null
3 3


27. What is the result of Select * from TABLEA A right join TABLEB B on a.ID = b.ID ?
Ans:
1 1
3 3
null 5

28. What are the types of indexes ?
Ans: Clustered and non-clustered index

29. What is the difference between clustered and non-clustered index ?
Ans: Clustered index is a special kind of index in which the node of the B tree has the actual value. so, clustered index can be created only on table that has unique non null values.
Non-Clustered index has a pointer to the actual node.

30. What is a getdate() ?
Ans: getdate() returns the current system timestamp.