Avg not updating from server
NET 2.0 with SQL Server 2005, SQL Server 2005's technique affords much better performance, taking on average a mere 3 milliseconds on average to return 10 records from a total table of 50,000.
Instead, we can create the table variable to hold only the primary key column(s) of the table to be paged through.
Comparing the Performance of the Old and New Approach To ascertain the improvements in performance by restricting the table variable to only including the primary key column(s) from the table to be paged through I used SQL Profiler and compared the different queries on the table with different record counts.
Keep in mind that these results are highly unscientific.
At first glance it may seem like such an approach would be no better than just returning all of the records.
After all, we're still working with all of the records in table's data in its entirety, the stored procedure only returns 10 records (or however many records per page are configured).
Search for avg not updating from server:
Since these records likely exist in a clustered index, populating the table variable likely be done entirely from memory, without needing to hit the disk.