SQL Server interview questions part 22
SQL Server interview questions part 22
Take as many assements as you can to improve your validate your skill rating
Total Questions: 20
1. The use of parameters, including parameter markers in _______ applications, can increase the reuse of execution plans.
Correct Answer is : All of the mentioned
2. Point out the correct statement :
Correct Answer is : Parameter markers are question marks (?) that replace a constant in an SQL statement
3. Use to bind an integer variable to the first parameter marker in an SQL statement.
Correct Answer is : SQLBindParameter
4. Point out the wrong statement :
Correct Answer is : MisGuided Plan Executions/sec represents number of plan executions per second in which the query plan has been generated by using a plan guide
5. Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second ?
Correct Answer is : Auto-Param Attempts/sec
6. The maximum size for all caches is a function of the ________ size.
Correct Answer is : Buffer pool
7. The conditions that invalidate a plan include the following :
Correct Answer is : Changes made to a table or view referenced by the query
8. What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL?
Correct Answer is : DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ].
9. _________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
Correct Answer is : plan_handle
10. Which of the following is not a ranking function ?
Correct Answer is : All of the mentioned
11. Point out the wrong statement :
Correct Answer is : OVER clause is not required in all the ranking functions
12. Which of the following is the simplest ranking function ?
Correct Answer is : ROW_NUMBER
13. Which of the clause is not mandatory ?
Correct Answer is : PARTITION BY clause
14. Which of the the function provides consecutive numbering except in the case of a tie ?
Correct Answer is : RANK
15. Which of the following will not raise error if not used ?
Correct Answer is : PARTITION BY clause
16. Which of the following error message will be displayed if ORDER CLAUSE is not mentioned in ROW NUMBER function ?
Correct Answer is : “The ranking function “ROW_NUMBER” must have an ORDER BY clause.”
17. Which of the following functions are similar ?
Correct Answer is : RANK and DENSE_RANK
18. Which of the following function is used when you want all tied rows to have the same ranking ?
Correct Answer is : RANK
19. SQL Server backup and restore operations occur within the context of the _______ model of the database.
Correct Answer is : recovery
20. Point out the correct statement :
Correct Answer is : Recovery models are designed to control transaction log maintenance
