Database and SQL

Performance is the primary motivation for denormalization - reducing joins improves read speed. Volatility is equally critical - frequently changing data causes update anomalies and maintenance overhead in denormalized structures. Downtime and stability are operational concerns, not architectural considerations for denormalization.

A referential constraint maintains relationships between tables by ensuring that values in a foreign key column match existing values in the parent table's primary key. The foreign key is defined on the dependent (child) table and points to the primary key of the referenced (parent) table. Check constraints validate column values, while triggers perform actions in response to events, but neither implements referential integrity.

CREATE TABLE newtab LIKE tab copies the column definitions and their data types from tab but does not duplicate indexes, constraints, or any existing rows, so the new table has the same columns and attributes only.

ORDER BY 1,2 sorts first by the first selected column (name) then by the second (cities) in ascending order by default; specifying ASC explicitly (1 ASC,2 ASC) does the same. Using column numbers 2,1 or 2 ASC,1 ASC would reverse the intended order.

The IN operator lets you test if a value matches any value in a list. For example, WHERE city IN ('Mumbai', 'Delhi', 'Chennai') returns rows where city equals any of those three values. BETWEEN checks for a range, not a list of discrete values.

UNION combines results from multiple SELECT statements into one result set. SQL requires all SELECTs in a UNION to have identical column count, compatible data types in each position, and the same column order. These structural rules ensure rows can be stacked vertically.

In SQL, aggregate functions like COUNT, SUM, AVG cannot be used directly in the WHERE clause because WHERE filters rows before aggregation. Instead, use HAVING after GROUP BY to filter based on aggregate results. This statement accurately describes that limitation.

Sybase IQ's column-oriented architecture treats every column as an indexed structure by default. Unlike traditional row-based databases that require explicit index creation, Sybase IQ stores data in highly compressed, indexed column structures. This architectural design is fundamental to its performance for analytical queries.

LF (Low Fast) index is optimized for columns with high cardinality like primary keys, providing efficient lookup performance. HG (High Group) is better for low-cardinality columns with many duplicates. HNG (High Non-Grouped) and CMP are not standard Sybase IQ index types for this use case.

Sybase IQ uses write locking where one process writing to a table blocks other processes from updating the same table. This prevents concurrent modification conflicts. The blocking process will fail or wait depending on isolation settings.

Selectivity estimates the fraction of rows matching a predicate, crucial for cost-based optimization. Usefulness indicates how effectively a search argument filters the result set. Together they guide the optimizer in choosing efficient access paths.

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.