SQL Server Database Optimization and Query Fundamentals

Indexes are ignored if the leading column is missing from search arguments, or if mismatched data types force implicit conversions. The optimizer may also bypass an index for a table scan if selectivity is low. Index names do not affect optimizer decisions.

Microsoft recommends a clustered index for almost every SQL Server table because it physically orders the data, greatly improving retrieval speeds for range queries and joins. Without a clustered index, the table exists as an unordered heap, which can lead to severe data fragmentation and slower lookups.

VARCHAR(50) is more efficient than CHAR(50) for state names because VARCHAR only uses storage space equal to the actual length of the data, while CHAR always allocates the full 50 bytes regardless of content length. For variable-length data like state names, VARCHAR reduces storage and improves cache efficiency. CHAR(10) is insufficient (some state names exceed 10 characters). NVARCHAR adds Unicode overhead unnecessarily for plain text. CHAR(100) wastes even more space.

Numeric columns generally make good index candidates because they are typically compact, fixed-width, and allow efficient comparisons. Numeric indexes enable fast range scans and sorting operations. However, this is not universally true - the selectivity of the column and query patterns matter more. Low-cardinality numeric columns like boolean flags may not benefit from indexing. But as a general principle, numeric columns are well-suited for indexes.

Index Scan (A) reads entire index instead of seeking specific values - indicates missing or non-selective index. Table Scan (B) reads entire table - worst performance indicator. Bookmark Lookup (C) or Key Lookup requires jumping from index to heap - expensive random I/O. Index Seek (D) is the efficient operation - it's NOT a performance problem indicator. These three (A, B, C) appearing in execution plans suggest missing indexes, non-selective predicates, or inappropriate index columns.

Dynamic Management Views (DMVs) provide runtime statistics about query performance, resource utilization, and indexes. DBCC commands like DBCC SHOW_STATISTICS and DBCC FREEPROCCACHE help diagnose and manage performance. Query Plans reveal actual execution strategies and bottlenecks. BCP (Bulk Copy Program) is a data import/export utility, not a performance diagnostics tool. The first three are essential for identifying slow queries and high CPU usage.

In a parent-child one-to-many relationship where the child column must always have a value, the child column (Table_B.Column_1) is a foreign key referencing the parent, must NOT allow NULLs, and is NOT unique (multiple child rows can reference same parent). Option D states 'Table_B.Column_1 should be unique' which is FALSE for a one-to-many relationship. Options A, B, and C all correctly describe the relationship: foreign key constraint, non-nullable child column, and proper indexing strategy.

Because the query frequently needs the most recent transaction per account, a clustered index on (Account_ID, Trans_Date) stores rows in that order, enabling fast retrieval of the latest date for a given account without additional sorting. A single‑column index or a non‑clustered index would be less efficient.

UNION ALL combines results from multiple tables without duplicate checking. Since the tables have mutually exclusive records, no duplicates exist, so the overhead of duplicate elimination in UNION is unnecessary. UNION is slower because it must sort and compare rows to remove duplicates. Separate SQL statements with front-end merging adds application complexity. A temporary table introduces unnecessary I/O and complexity. UNION ALL is the most efficient approach for mutually exclusive data.

Query hints should only be used as a last resort when the optimizer fails to choose an efficient plan. Hints like NOLOCK can return uncommitted data (dirty reads). UPDATE locks can cause unintended blocking. Once hints are added, they persist even if data distribution changes and the optimizer would have chosen a better plan. Best practice is to trust the query optimizer unless there's a proven performance issue. The statement is false.

In Oracle and most relational databases, DDL statements like CREATE TABLE commit automatically and implicitly. Because of this implicit commit, a subsequent ROLLBACK command has no effect on the table's existence. Therefore, the DESC command will successfully describe the table structure.

GROUP BY is used with aggregate functions like COUNT, SUM, AVG, MAX, MIN to perform calculations on groups of rows. The columns in GROUP BY determine how rows are grouped, and aggregates compute summary values for each group. Non-aggregate columns in the SELECT must appear in the GROUP BY clause itself.

The UPDATE statement syntax requires specifying the table name directly after UPDATE, not using the TABLE keyword. Option B correctly shows 'UPDATE TABLE_NAME SET column_name=value WHERE condition'. Option A incorrectly includes 'TABLE' as a keyword before the table name.

UNION eliminates duplicate rows from the combined result set, effectively performing a DISTINCT operation. UNION ALL preserves all rows including duplicates, which makes it faster and useful when you know there are no duplicates or want to keep them. The statement correctly describes this key difference.

Bitmap indexes are highly efficient for columns with low cardinality, where data is repetitive and contains many duplicate values (such as gender or marital status) across large tables. They use bit arrays to perform fast boolean operations, making them ideal for data warehousing environments.

The SPLIT string function was NOT available in SQL Server 2005. It was introduced in SQL Server 2016. Earlier versions required workarounds using CHARINDEX, SUBSTRING, XML methods, or custom user-defined functions to split strings.

DDL stands for Data Definition Language, which includes SQL commands that define database structures like CREATE, ALTER, DROP for tables, indexes, views, and schemas. It deals with schema definition rather than data manipulation (DML) or data control (DCL). Option D uses Link incorrectly, and B uses Description instead of Definition.

sp_xml_preparedocument is the correct system stored procedure that parses an XML document and returns a handle that can be used with OPENXML. The other options are虚构的 names that don't exist in SQL Server. After use, the document handle should be freed with sp_xml_removedocument to avoid memory leaks.

DBCC CHECKTABLE checks the integrity of a table or indexed view, verifying page linking, structural integrity, and performing internal consistency checks. CHECKIDENT checks identity values, CHECKALLOC verifies allocation structures, and CHECKINTEG isn't a valid DBCC command.