Tuesday, December 21, 2010

SQL Server Interview Questions and Answers : Series 5

Question: What is the User defined functions?
Answer: User defined functions are the objects which used to for performs some particular task or retrieves some particular data. Function always returns some values.
Question: What are the difference types of UDF?
Answer: Udf are of two type’s single value return function and tabular function. Tabular function returns record set while single value function returns only a single value.
Question: What are difference between UDF and stored procedure?
Answer: Udf returns a value and SP generates record set or output parameter. SP can be used for DDL and DML statement while UDF cannot be used for the same.
Question: What is the cursor in SQL server?
Answer: Cursors are used to hold the data temporarily for processing for some logic. It stores the data from table and process one by one each record.
Question: What are the different types of cursor in SQL Server?

Question: When to use cursor in SQL Server?
Answer: Cursor is required when there is any operation required on data by traversing one by one.
Question: What is a trigger in SQL Server?
Answer: Triggers are the objects which used to perform the 
Question: What are the different types of triggers in SQL Server?
Answer: After and Instead of Triggers are the two types of triggers in sql server.
Question: What is the Ranking function in SQL Server?
Answer: Ranking functions were introduced in sql server 2005 and the function can be used for getting the rank.
Ranking functions are four types.
1.       Row number
2.       Rank
3.       Dense Rank
4.       Ntile
Question: What is top operator in SQL Server?
Answer: Top operator is used in SQL server to get some specified no of rows from the result set.

Question: What is table sample in SQL Server?
Answer: Table sample is used for getting the sample of rows from a result set. This sample is in the form of percentage.
  Ex Select table sample (10%) from table A
Question: What is the delete command?
Answer: Delete command is used to delete the data from the table. It deletes the data one by one.
Question: What is truncate command?
Answer: Truncate command is used to delete the data from the table.
Question: Difference between delete and truncate?
Answer: Data deleted with truncate command cannot be roll back one the transaction is committed. While data deletion with delete command can be roll back even if the transaction is committed. I.e. for truncate command transaction logs are not maintained while for delete command transaction logs are maintained as delete operation performs row by row.


1 comment:

  1. Truncate can be rolled back as well