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?
Lets leave my test aside. Lets leave the view aside. A new question has arisen from test created by Grant. Grant ran his test and then quoted: Insert takes "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?
SQL Server basics, already answered:
https://www.sqlservercentral.com/Forums/FindPost2002447.aspx
Okay. What you are saying is: On a standalone machine, with database in single-user mode, writing to a real table in the user database is 5 times slower than writing to a temp table/table variable in the tempdb?
Yes, because a real table has to be written to disk. I'm surprised you are unaware of this. There's also a simple explanation for the write to table variable being faster than the write to temp table.
Real table has to written to disk, is it not required for temp table/table variable to be written to disk? All three have physical storage. Real table in user database and temp table/table variable in tempdb.
If memory is not limiting, neither table variable nor temp table will be written to disk - but a placeholder for temp table will be created. Table variable changes are also not logged.