SWC_QUIZ_4

Which SQL statement is used to create an index on a column?

• CREATE TABLE
• CREATE INDEX
• CREATE VIEW
• CREATE CONSTRAINT
• CREATE COLUMN

Answer explanation

CREATE INDEX is used to create an index on a table column.

What is the main benefit of using indexes?

• Data redundancy
• Faster query performance
• Smaller table size
• Avoid joins
• Normalization

Answer explanation

Indexes improve query speed, especially for SELECT statements with WHERE.

Which of the following can negatively impact performance when overused?

• Views
• Constraints
• Indexes
• Triggers
• Foreign keys

Answer explanation

Too many indexes can slow down INSERT, UPDATE, and DELETE operations.

How can you see whether a query uses an index in SQL Server?

• SHOW PLAN
• USE INDEX
• EXPLAIN QUERY
• Query Plan Viewer
• LOG INDEX

Answer explanation

SQL Server's Query Plan Viewer shows if indexes are being used.

What happens when you drop an index?

• Table is deleted
• Data is lost
• Query performance may degrade
• Triggers are disabled
• Nothing

Answer explanation

Dropping an index can reduce read performance.

Which of the following is TRUE about CTEs in SQL?

• They persist data permanently
• They are used only for INSERT
• They are temporary result sets
• They replace triggers
• They are stored procedures

Answer explanation

CTEs are temporary and exist only during query execution.

What is a key difference between Views and CTEs?

• Views are faster
• CTEs require stored procedures
• Views are persistent, CTEs are not
• CTEs use triggers
• Views can’t use joins

Answer explanation

Views are saved in the database; CTEs are not.

When should you use a Materialized View?

• When data never changes
• When real-time updates are needed
• When performance matters more than freshness
• When you dislike indexes
• To drop tables

Answer explanation

Materialized Views store query results for faster performance.

Which is more reusable: a CTE or a View?

• CTE
• Neither
• View
• Both are equally reusable
• Stored procedure

Answer explanation

Views are saved and can be reused; CTEs cannot.

Which option allows referencing itself recursively in SQL?

• View
• Materialized View
• Recursive CTE
• Trigger
• Function

Answer explanation

Recursive CTEs are used for hierarchical or tree-like data.