SQL Server interview questions part 13
SQL Server interview questions part 13
Take as many assements as you can to improve your validate your skill rating
Total Questions: 20
1. Which of the following pair of views has one to many relationship ?
Correct Answer is : sys.server_event_session_actions.event_session_id,sys.sys.server_event_sessions.event _session_id
2. Which of the following format output events in an Event Tracing for Windows ?
Correct Answer is : etw_classic_sync_target
3. Point out the correct statement :
Correct Answer is : In HierarchyID, we create indexes in order to make the traversal efficient
4. Point out the wrong statement :
Correct Answer is : Stored procedures are the easiest way to extract hierarchical data
5. What is purpose of GetDescendant method in the following code ? DECLARE @parent HierarchyId = HierarchyId::GetRoot() INSERT INTO H (Node,ID,Name) VALUES (@parent.GetDescendant(NULL,NULL),2,'Johnny')
Correct Answer is : Takes 2 arguments
6. Which of the following function returns true in the following code ? DECLARE @parent HierarchyId = (SELECT Node FROM H WHERE Name = 'Thuru') DECLARE @parentNodeLevel INT = (SELECT NodeLevel FROM h WHERE name = 'Thuru') SELECT Node.ToString() AS NodeText, * FROM H WHERE Node.IsDescendantOf(@parent) = 'TRUE' AND Node != @parent AND NodeLevel = @parentNodeLevel + 1
Correct Answer is : IsDescendantOf
7. Which of the code deletes node H using hierarchical data type?
Correct Answer is : DELETE FROM H WHERE Name = ‘Steve’
8. Which of the following function will be used in the following code for moving nodes ? DECLARE @newParent HierarchyId = (SELECT Node FROM H WHERE name = 'Johnny') UPDATE H SET Node = Node.__________(Node.GetAncestor(1),@newParent) WHERE Name = 'S1'
Correct Answer is : GetReparentedValue
9. Setting the SORT_IN_TEMPDB option to ON can direct the SQL Server Database Engine to use ________ to store the intermediate sort results.
Correct Answer is : tempdb
10. Point out the correct statement :
Correct Answer is : The Database Engine merges the sorted runs of index leaf rows into a single, sorted stream
11. Which of the following guideline is considered during column design ?
Correct Answer is : An xml data type can only be a key column only in an XML index
12. Point out the wrong statement :
Correct Answer is : SQL Server supports only two index types: clustered and non-clustered
13. By default, indexes are stored in the ______ filegroup as the base table on which the index is created.
Correct Answer is : same
14. Specifying the order in which key values are stored in an index is useful when queries referencing the table have :
Correct Answer is : ORDER BY clauses
15. Every table should have a ________ index defined on the column, or columns.
Correct Answer is : CLUSTERED
16. Which of the the following option is immediately applied to the index by using the SET clause in the ALTER INDEX statement ?
Correct Answer is : ALLOW_PAGE_LOCKS
17. Point out the correct statement :
Correct Answer is : The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row
18. Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table ?
Correct Answer is : DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
19. Which of the following query can enable all indexes on Employee table ?
Correct Answer is : DBCC DBREINDEX (“Employee”, ” “);
20. Which of the following query disables all the indexes on Employee table ?
Correct Answer is : ALTER INDEX ALL ON HumanResources.Employee
