You can perform hash-based index scans using nonclustered indexes or clustered indexes on data-only-locked tables. During this scan, each worker process navigates the higher levels of the index and reads the leaf-level pages of the index. Each worker process then hashes on either the data page ID or the key value in a separate hash table to determine which data pages or data rows to process.
A virtually-hashed table can be a more efficient way to organize a table because it does not require a separate hash table. Instead, it stores the rows so that, using the hash key, the query processor can determine the row ID (based on the row’s ordinal number) and the location of the data. Because it does not use a separate hash table to hold the information, it is called a “virtually” hashed table.
For systems that must make more efficient use of their central-processing unit (CPU), the virtually hashed table is a good option.
It is expensive for tables used for look-ups or for tables whose row position does not change, to use a clustered or nonclustered index. With recent advancements in level-2 and level-3 (L2 and L3) CPU architectures, you must utilize the cache to take advantage of the real CPU computing power. If you do not utilize the cache, the CPU spends needless cycles waiting for available memory. For clustered or non-clustered indexes, the server misses rows every time it accesses the index-level search, which consumes many CPU cycles. Virtually-hashed tables access row-location patterns by computing the hash-key value instead of performing a search.
