Every UPDATE and DELETE on a system-versioned table leaves behind a complete, queryable history of the previous row — automatically, with no triggers and no audit-table plumbing. SQL Server records the period each row version was valid and answers point-in-time questions through a single FOR SYSTEM_TIME clause. This is …

Documenting a database starts with a data dictionary: every table, every column, its data type, length, nullability, and default. SQL Server exposes this through the ANSI-standard INFORMATION_SCHEMA views, which produce a portable schema report that reads almost like English and runs unchanged on other ISO-compliant …

A query that ran in milliseconds last month now scans a million rows and spills to tempdb. Nothing changed in the code — but the data grew, and the statistics the optimizer relies on went stale, so its row estimates drifted far from reality and it chose a bad plan. Refreshing statistics is the fix, and knowing how and …

Which columns make up a table's primary key, and in what order are they declared? For a single table, the system stored procedure sp_pkeys answers in one line; for a database-wide inventory, the catalog views behind it give you full control. This post covers both, from the quick lookup to the complete metadata query. …

Plenty of SQL Server scripts still in circulation read rowcnt and dpages straight out of sysindexes — a habit inherited from SQL Server 2000, where that table was the canonical place to find row counts and page totals. The object survives only as the sys.sysindexes compatibility view, is flagged for removal, and …

The sys catalog views are the supported, forward-compatible way to read SQL Server metadata, and three of them — sys.objects, sys.tables, and sys.indexes — answer most day-to-day questions about what lives in a database. This script joins them into one inventory of every user table, its schema, its row count, and each …

Every SQL Server instance ships with a parallel command vocabulary that never appears in an ORM, a stored procedure, or an application query: the DBCC statements. They validate physical page structure, reclaim file space, evict cached plans, reseed identity columns, and report internal counters that no SELECT exposes — …

Corruption rarely announces itself. A bit flips on a SAN, a controller loses power mid-write, a driver mishandles a flush — and the damaged page sits unread for weeks until a query finally touches it and the database throws error 824. By then the corruption may already be inside every backup you keep. DBCC CHECKDB is …

A stored procedure that returned in 20 milliseconds yesterday is timing out today, and nothing in the code changed. The data did not grow meaningfully, the indexes are intact, and statistics look current — yet the query is suddenly scanning a million rows it used to seek. The usual culprit is a cached execution plan …

A one-time data load doubled a data file overnight, the load is done, the rows are deleted, and the file is now mostly empty space. The reflex — reach for DBCC SHRINKDATABASE and give the disk back — is one of the most common self-inflicted performance wounds in SQL Server administration. This post explains exactly …