You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds). Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?
Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.
Agreed, writing to disk is slower than reading from disk. Real table has a physical storage on disk in user database, temp table and table variable have physical storage on disk in tempdb database. Writing to a table in user database (in single user mode) on a standalone machine should be as quick to writing to a table in tempdb database. tempdb is a shared resource, so lets have only one connection to server.
Lets run the test created by Grant like this:
1) Standalone machine.
2) Only one instance of SQL Server (default instance).
3) Only one connection to the server (close all other others), so that tempdb contention is not there.
4) User database is in single user mode.
5) Connect to the user database.
6) Run the test created by Grant.
Writing to real table should be as quick as writing to table in tempdb, as there is no tempdb contention. If its not, then why?
You know for certain that Microsoft WILL NOT REWARD YOU for disclosing whatever you've found.
Are there any other reasons why you might want to withhold your view definition?