gs gs116 Performance Tuning and Optimization - Indexing Strategies - Quiz No.2
gs gs116 Microsoft SQL Server Quiz
This quiz belongs to book/course code gs gs116 Microsoft SQL Server of gs organization. We have 100 quizzes available related to the book/course Microsoft SQL Server. This quiz has a total of 10 multiple choice questions (MCQs) to prepare and belongs to topic Performance Tuning and Optimization. NVAEducation wants its users to help them learn in an easy way. For that purpose, you are free to prepare online MCQs and quizzes.
NVAEducation also facilitates users to contribute in online competitions with other students to make a challenging situation to learn in a creative way. You can create one to one, and group competition on an topic of a book/course code. Also on NVAEducation you can get certifications by passing the online quiz test.
Question 1: Which of the following query is used to enable a disabled index using CREATE INDEX?
CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee (OrganizationLevel, OrganizationNode) WITH (DROP_EXIST = ON); CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee (OrganizationLevel, OrganizationNode) WITH (DROP_EXISTING = OFF); CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee (OrganizationLevel, OrganizationNode) WITH (DROP_EXISTING = ON);
None of the mentioned
Question 2: Point out the correct statement.
When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the source filegroup
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
When SORT_IN_TEMPDB is set to ON, the default, the sort runs are stored in the destination filegroup
None of the mentioned
Question 3: Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table?
DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
DBCC DBINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
All of the mentioned
Question 4: Which of the following query can enable all indexes on Employee table?
DBCC DBREINDEX (“Employee”, ” “);
DBCC DBREINDEX (“EMP”, ” “);
DBCC DBREINDEX (“HumanResources.Employee”, “ALL”);
None of the mentioned
Question 5: Point out the wrong statement.
If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure
When you create a clustered index on a table that has nonclustered indexes, you must have available as free space
When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in master database
All of the mentioned
Question 6: Which of the following query disables all the indexes on Employee table?
ALTER INDEX MUL ON HumanResources.Employee DISABLE;
ALTER INDEX NEST ON HumanResources.Employee DISABLE;
ALTER INDEX ALL ON HumanResources.Employee ENABLE;
ALTER INDEX ALL ON HumanResources.Employee DISABLE;
Question 7: Which of the following index operations require no additional disk space?
ALTER INDEX
DROP INDEX
CREATE INDEX
All of the mentioned
Question 8: Which of the following index operation require additional disk space?
UPDATE INDEX
DROP INDEX MOVE TO
ALTER INDEX ADD CONSTRAINT
All of the mentioned
Question 9: The SORT_IN_TEMPDB option cannot be set for ________ statements.
ALTER INDEX
DROP INDEX
CREATE INDEX
All of the mentioned
Question 10: Which of the following query will disable the index on Employee table?
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee DISABLE;
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources DISABLE;
CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee DISABLE;
None of the mentioned
Online Quizzes of gs116 Microsoft SQL Server
Developing with SQL Server - Error Handling - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Aggregation of Data - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Joins in SQL Server - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
Laying the Foundation - Views in SQL Server - Quiz No.1
gs gs116
Microsoft SQL Server Online Quizzes
