MS SQL Server - 40 - Create Edit Indices MCQ

40 – Create Edit Indices MCQ

Best of luck!

1 / 25

1. What is the primary purpose of creating an index on a SQL Server table?

To store data separately from the table

To create a backup of the table

To improve query performance by speeding up data retrieval

To enforce primary key constraints

2 / 25

2. Which SQL statement is used to create a nonclustered index on a table in SQL Server?

CREATE CLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName

CREATE UNIQUE INDEX IX_IndexName ON TableName (ColumnName)

3 / 25

3. What is the primary advantage of creating a clustered index in SQL Server?

Enhanced data integrity

Faster data modification operations

Reduced storage space requirements

Improved data retrieval performance for specific queries

4 / 25

4. Which system view in SQL Server provides information about existing indices on a table?

sys.objects

sys.indexes

sys.tables

sys.columns

5 / 25

5. In SQL Server, what is the purpose of the UNIQUE keyword when creating an index?

It specifies the index storage location.

It creates a clustered index.

It enforces uniqueness for the indexed column(s).

It allows duplicate values in the indexed column(s).

6 / 25

6. Which SQL statement is used to edit the properties of an existing index in SQL Server?

MODIFY INDEX

UPDATE INDEX

ALTER INDEX

EDIT INDEX

7 / 25

7. In SQL Server, what is the primary purpose of the ALTER INDEX…REBUILD statement?

To rename an index

To recreate the index to improve fragmentation

To change the index type from clustered to nonclustered

To drop an index

8 / 25

8. Which statement is used to change the name of an existing index in SQL Server?

ALTER INDEX

RENAME INDEX

MODIFY INDEX

EDIT INDEX

9 / 25

9. In SQL Server, what is the purpose of the INCLUDE clause when altering an index?

To add additional columns to the leaf level of the index

To specify the index type (clustered or nonclustered)

To change the fill factor of the index

To drop the index

10 / 25

10. What is the primary advantage of using a resumable online index creation in SQL Server?

It speeds up data modification operations.

It reduces storage space requirements.

It allows the index creation to be paused and resumed.

It enforces primary key constraints.

11 / 25

11. Which SQL statement is used to create an index with resumable online mode in SQL Server?

CREATE INDEX IX_IndexName ON TableName (ColumnName) WITH (ONLINE = ON)

CREATE RESUMABLE INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName) WITH (RESUMABLE = ON)

CREATE INDEX IX_IndexName ON TableName (ColumnName)

12 / 25

12. In SQL Server, what is the primary advantage of removing an index?

It speeds up data retrieval operations.

It enforces primary key constraints.

It reduces storage space requirements.

It enhances data integrity.

13 / 25

13. Which SQL statement is used to remove an index from a table in SQL Server?

DROP INDEX IX_IndexName ON TableName

DELETE INDEX IX_IndexName FROM TableName

ALTER TABLE TableName DROP INDEX IX_IndexName

REMOVE INDEX IX_IndexName FROM TableName

14 / 25

14. In SQL Server, what is the primary purpose of renaming an index?

To provide a more descriptive name

To enforce uniqueness

To change the indexed column(s)

To improve query performance

15 / 25

15. Which SQL statement is used to rename an index in SQL Server?

MODIFY INDEX OldIndexName RENAME TO NewIndexName

RENAME INDEX OldIndexName TO NewIndexName

ALTER INDEX OldIndexName RENAME TO NewIndexName

ALTER INDEX NewIndexName RENAME TO OldIndexName

16 / 25

16. What is the primary purpose of altering an index in SQL Server?

To drop an index

To modify the properties of an existing index

To create a new index

To change the table's structure

17 / 25

17. In SQL Server, which SQL statement is used to alter the fill factor of an existing index?

CHANGE INDEX … FILLFACTOR

ALTER INDEX … SET FILLFACTOR

MODIFY INDEX … FILLFACTOR

SET INDEX … FILLFACTOR

18 / 25

18. What is the primary purpose of the ONLINE keyword when altering an index in SQL Server?

To create a new index

To change the index type from nonclustered to clustered

To enforce primary key constraints

To perform the alteration without locking the entire table

19 / 25

19. Which SQL statement is used to change the fill factor of an existing index in SQL Server?

ALTER INDEX … SET FILLFACTOR

SET INDEX … FILLFACTOR

MODIFY INDEX … FILLFACTOR

CHANGE INDEX … FILLFACTOR

20 / 25

20. What is the primary purpose of the DROP INDEX statement in SQL Server?

To modify an index

To create an index

To rename an index

To remove an index from a table

21 / 25

21. In SQL Server, which SQL statement is used to drop a clustered index?

REMOVE CLUSTERED INDEX IX_IndexName FROM TableName

DELETE INDEX IX_IndexName FROM TableName

DROP CLUSTERED INDEX IX_IndexName ON TableName

DROP INDEX IX_IndexName ON TableName

22 / 25

22. In SQL Server, which SQL statement is used to drop a nonclustered index?

DELETE INDEX IX_IndexName FROM TableName

DROP INDEX IX_IndexName ON TableName

DROP NONCLUSTERED INDEX IX_IndexName ON TableName

DROP CLUSTERED INDEX IX_IndexName ON TableName

23 / 25

23. What is the primary advantage of using a clustered index in SQL Server?

It enforces primary key constraints.

It speeds up data retrieval operations for specific queries.

It allows duplicate values.

It reduces storage space requirements.

24 / 25

24. In SQL Server, what is the primary purpose of renaming an index?

To enforce uniqueness.

To provide a more descriptive name

To change the indexed column(s)

To improve query performance

25 / 25

25. Which SQL statement is used to rename an index in SQL Server?

ALTER INDEX NewIndexName RENAME TO OldIndexName

RENAME INDEX OldIndexName TO NewIndexName

ALTER INDEX OldIndexName RENAME TO NewIndexName

MODIFY INDEX OldIndexName RENAME TO NewIndexName

Your score is

MS SQL Server – 40 – Create Edit Indices MCQ