Would you expect this then ?
I've just been working through a database looking for log tables which can have data aged out, or deleted if you like. I was using SSMS for this and used the default right click select top 1000 rows as a quick view of the data.
Establishing that I had unwanted data in such a table I deleted based upon getdate()-30 , nearly 7,000 rows deleted, I then highlighted the top 1000 rows query and re-ran query, surpisingly I still got the same result set with 2006 dates in it, and 1,000 rows.
A quick count(*) from the table showed only 719 rows, a select * showed all data prior to 30 days ago deleted. Re-ran the original query, still 1000 rows with dates of 2006, all this in the same query window in SSMS
No worries then, open another query window, right click, top 1000 rows --- still 1000 rows returned and showing data of 7,00 odd rows. Table properties also showed 7,000 rows.
Back to my original query, a count (*) reports 719 rows. I can only assume that SSMS caches any right click Select TOP 1000 * queries and even another Query windows does not reflect the updated data set, which is a bit strange, I mean you'd normally expect a select to see data followed by a delete of some rows form the table, followed by a select would show, so to speak, that rows had actually been deleted - well I did.
So if anyone can come with a logical explnation of how I have just opened a new window in SSMS, run the select top 1000 from table and returned 1000 rows which don't exist in the table, followed by a select * in the same window which only returns the 719 rows and none of the rows the previous top 1000 returned I'd be really interested.
SQL 2008 SP1 CU6