I'll address a couple of concerns from my point of view.
1. Based on the article, the first 4 values should be zero. This is why I put an isnull in my code. If you take that out, the first four values will be null.
2. The identity column. Again, based on the article, the sole purpose of the identity column is to provide an order to the values inserted into the table. By making this column a primary key, I also made this a clustered index allowing it to force the order specified. By Jeff's way of doing this, I also should have put an index hint on the update query as well, but for this demonstration it worked well without it. Since the purpose of the identity field was order, if there was a gap it wouldn't have caused an issue.
Some of the other things that have been brought up are actually extensions of this article, and I challenge those individuals to pursue those and show us what you find, either as a post here, or write another article expanding on what the author started.
This is how we learn new tricks and ways to accomplish a variety of tasks.
I reran my queries, once with the initial data set, and a second time with a larger data set. Below are the row counts, statistics io, ans statistics time from the runs.
row count: 20
-- Cross Join Query --
Table 'Accounts'. Scan count 17, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 44 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Select After Update Query --
Row Count: 3280
-- Cross Join Query --
Table 'Accounts'. Scan count 3278, logical reads 6663, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 246 ms.
-- Cross Join Query --
-- Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 35 ms.
-- Update Join Query --
-- Select After Update Query --
Table 'Accounts'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 38, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 201 ms.
-- Select After Update Query --