| Resumability | Source and destination row counts per chunk value are snapshotted once, up front, in a single GROUP BY scan per side, not re-queried per chunk. A chunk whose counts already match is skipped, a re-run after a partial failure only touches what's left, with no primary/unique key required anywhere on the table. |
| Interrupted mid-chunk | If a chunk is killed mid-copy (not between chunks), whatever leftover rows already landed for that one chunk are deleted before it's retried, so a retry can never double up rows, even without a key to prevent duplicates. |
| Automatic chunk column | -ChunkColumn is optional. Get-sqmChunkColumnCandidate ranks date-typed and period-named columns by naming convention and estimates each candidate's chunk count from the column's statistics histogram, a metadata read that costs the same on a 344-million-row table as on an empty one. INT, BIGINT, CHAR or VARCHAR columns holding dates as YYYYMMDD (e.g. 20260115) are recognised too, whatever they are called: every key of their statistics histogram is a valid date. The choice, its estimate, and the reason are logged; with no suitable candidate the run aborts and names what it rejected and why, instead of guessing. |
| Month granularity | A day-level column would mean one chunk per day, thousands of tiny chunks over a few years. -ChunkGranularity Auto (the default) switches day-level columns, YYYYMMDD surrogates and date columns with clearly more values than months, to one chunk per calendar month, also when -ChunkColumn is given explicitly. The chunk filter stays a range on the bare column (BOOKDATE >= 20260100 AND BOOKDATE < 20260200, the date equivalent, or LIKE '202601%'), so an index on the column is still used for a seek. -ChunkGranularity Value keeps one chunk per distinct value; Month on a column without date values stops with a clear message. |
| Constraint handling | Foreign keys, indexes, and triggers are disabled once for the whole chunked run and rebuilt once at the end, not per chunk. Rebuilding an index is a whole-table operation regardless of the chunk size, so doing it per chunk instead of once is the difference between minutes and hours on a large table. |
| Reads each chunk by seek | On a heap, or a clustered index that does not lead with the chunk column, SQL Server answers WHERE <chunk range> on a large table with a full table scan, even when an index on the chunk column exists, so every chunk read the whole source table, and the destination's INSERT BULK session sat in ASYNC_NETWORK_IO while the scan passed other chunks' rows. Since 0.1.23.0 -SourceAccess Auto reads each chunk WITH (FORCESEEK) over an index leading with the chunk column (from 4 chunks on, after a probe that the seek can be built). Measured end to end on a lab heap with 358 day chunks: about 30 s per chunk by scan, about 6 s by seek; the gap grows with table size. |
| No connection leak | Up to 0.1.22.0 every chunk left one connection open until the process ended. After about 100 chunks the connection pool (default size 100) was exhausted and every further chunk failed after a 15-second wait. Fixed in 0.1.23.0: the session count stays flat for any number of chunks. |
| Fails fast | When a chunk fails mid-copy (bulk-copy timeout, a constraint violation or a full disk on the destination), the source query is cancelled on the server. Before 0.1.22.0 the reader was closed without cancelling, which reads and discards every remaining row of the chunk first: measured on a lab instance, a failure after 15,000 of 600,000 rows took 313 seconds to surface, now 5 seconds. |
| Column-safe by construction | Each chunk's SELECT is bulk-copied with an explicit name-based column mapping (Invoke-sqmDirectBulkCopy), immune to column order or computed columns on either side, and independent of which dbatools version happens to be installed. |
| Columnstore-safe batching | A destination with a columnstore index gets its effective batch size capped below the 102,400-row threshold at which SQL Server compresses a batch directly into a rowgroup instead of routing it through the delta store, expensive, and synchronous in the load path on COLUMNSTORE_ARCHIVE. Detected automatically, no configuration needed. |
| Renaming on the way in | -DestinationTable loads into a differently-named destination, such as a freshly re-partitioned "_New" copy of a table ahead of a rename-swap cutover. |
| Cheap verification | The final consolidated row-count comparison reads SQL Server's own partition metadata instead of scanning, accurate once the run has actually finished, with no COUNT(*) cost at any table size. |
