CompTIA DataSys+ (DS0-001) Practice Tests

Explanation

The Invoke-Sqlcmd cmdlet from the SqlServer PowerShell module is the correct native Microsoft approach for automating SQL Server database tasks via PowerShell. It accepts -ServerInstance to target specific SQL Server instances, -Database for scope, and -Query to execute T-SQL commands — making it ideal for scripting index maintenance across multiple servers. The SqlServer module is installed via Install-Module -Name SqlServer and supports both Windows Authentication and SQL Authentication. The psql tool is a PostgreSQL command-line client, not a SQL Server tool, and bash scripting would not be the primary choice on Windows Server. The sqlcmd utility is a valid SQL Server tool but is a standalone command-line program, not a PowerShell cmdlet — integrating it into PowerShell scripts requires more workaround code and is less idiomatic than Invoke-Sqlcmd. The bcp utility is designed for bulk data export and import, not for executing maintenance queries or checking fragmentation levels.

Explanation

SQL Server 2016 and later with the dynamic statistics update threshold enabled (on by default with database compatibility level 130+) uses the formula: minimum of (500 + 0.20 × rows) and SQRT(1000 × rows). For a 2 million row table, SQRT(1000 × 2,000,000) = SQRT(2,000,000,000) ≈ 44,721 modifications, which is much lower than the old 20% threshold. At 85,000 modifications, the threshold should already have triggered an update — suggesting compatibility level may be below 130 or the setting is disabled.

Explanation

Index-free adjacency is the core architectural feature that makes graph databases like Neo4j extremely fast at relationship traversal. Each node in the graph physically stores direct pointers to its adjacent nodes and relationships. This means traversing from one node to its neighbors requires only following a pointer, achieving O(1) time per hop regardless of total graph size. In contrast, a relational database must perform a JOIN operation and index lookup for each relationship traversal, with performance degrading as the dataset grows. BSON document storage is used in MongoDB, not Neo4j. Column-family storage is the architecture of Cassandra, not a graph database. Hash-based indexing that achieves O(log n) traversal would be significantly slower than Neo4j's O(1) adjacency and would not provide the sub-millisecond per-hop performance described.

Explanation

Fill factor controls how full index pages are when built or rebuilt, leaving free space for future insertions. For InvoiceHistory with a sequential auto-incrementing integer key, all new rows are inserted at the end of the index (the last page). This means pages in the middle of the index will never receive new inserts, so they can safely be filled to 100% with no free space left. Setting fill factor to 100 maximizes storage efficiency for this pattern. For ProductCatalog with a random GUID primary key, new rows are inserted at random positions throughout the entire index because GUIDs have no sequential ordering. This causes frequent page splits — when an index page is full and a new row must be inserted in the middle, SQL Server must split the page into two, which is expensive. Leaving free space (80-90% fill factor) absorbs these mid-index insertions and reduces page splits. A fill factor below 50 is generally never recommended as it wastes too much storage.

Explanation

The CustomerAddress index at 35% fragmentation crosses the standard threshold of 30%, which triggers an index rebuild using ALTER INDEX REBUILD. SQL Server index maintenance follows a three-tier model based on avg_fragmentation_in_percent from sys.dm_db_index_physical_stats: fragmentation below 10% requires no action because overhead outweighs benefit; fragmentation between 10% and 30% warrants a REORGANIZE operation, which is always online and compacts leaf-level pages without updating statistics; fragmentation above 30% warrants a REBUILD, which drops and recreates the index, removes all fragmentation, and automatically updates statistics with a full scan equivalent to UPDATE STATISTICS WITH FULLSCAN. Leaving a 35% fragmented index untreated is incorrect because this level of fragmentation causes measurable I/O overhead on range scans. Reorganizing at 35% is the wrong operation for this fragmentation level since reorganize is the appropriate choice only in the 10-30% range. Dropping and manually recreating the index is unnecessary because ALTER INDEX REBUILD accomplishes the same result with less complexity and supports an online option in Enterprise Edition.