Everytime a good product comes out, people seem to contrive bad ideas for what can be replaced, usually brains, or people with brains, or that people lacking brains can accomplish something on their own.
Let me put it simply. Suppose one had a query that uses a nonclustered index, and required a key lookup, and the key lookup required a disk access.
Key lookup, disk access, 10 HDD: 2000-3000 lookups per sec (4K at high Q depth)
Key lookup, in memory: 200,000 lookups per second per core (2.66GHz Core2)
Key lookup, disk access, SSD array: 40,000 lookups per sec per core (same CPU)
Covered/Included index, in memory: 1-2M rows per sec.
It takes brains to build SQL and indexes to achieve the smallest practical memory footprint, and one that allows the most efficient CPU operations, meaning: 1) minimal avoidable key lookups, 2) enabling merge or hash joins when appropriate over loop joins.
The SQL Server internal cost based optimizer also makes the assumption that disk IO costs are 0.000740741 (seconds) per incremental page in a table scan (1350 pages/sec) and 0.003215 per key lookup (pseudo random 8K) for 320 per sec, about a 4.2 to 1 ratio in scan versus lookup. These numbers already do not reflect current generation disk drives (90-120MB/sec, 200-300 random IOPS). Suppose the new Intel SSD can do 160MB/sec irrespective of whether the access is sequential or small block random. Will MS change the CBO to a 1:1 ratio? And will it do so correctly depending on the whether the data file is on HDD or SSD? If not, then even if the SSD is very fast, SQL is using the wrong plan!
Note: In a poorly designed database/index set, hash and merge joins occur with a scan on one or more sources, and if the scan is big, the query is will be very expensive. So brain deficient people associate hash and merge joins with expensive. If one had brains, one would realize what was expensive was the big scan. The reason for the hash or merge operation is that they are more efficient join mechanisms on a per row basis than a loop join, except for low row counts.