Database and SQL

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.

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.

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.

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.

SQL Server functions are called using schema qualification: DatabaseName.SchemaOwner.FunctionName(). The default schema owner is dbo, so DatabaseName.dbo.FunctionName() is correct syntax. The schema qualification is required for UDFs (User-Defined Functions) when calling from outside the function's database.

Transact-SQL reserves certain keywords that have special meaning in the language. datetime is a data type keyword, hierarchyid is a data type for hierarchical data, and status is a reserved keyword. views and value are NOT reserved keywords in T-SQL. Knowing reserved words is important to avoid naming conflicts.

sp_rename 'OldName', 'NewName' is the correct system stored procedure to rename a table (or other database objects). The first parameter is the current name, the second is the new name. Options A and D reverse the order, and sp_renametable doesn't exist - only sp_rename.