[Tricks and Tips] a second way to indirectly view rows in a table

So in my last post I came up with a way to use DBCC IND and DBCC PAGE to view pages indirectly, so the stats don't get updated. 


Here's a simpler way, albeit with more impact.

Make an index, then look at the histogram.

For instance, I have an auditing table.  I want to see the last time rows were inserted into it.

In this case, we do two things:

CREATE INDEX NCIDX_TABLENAME_FIELDNAME ON TABLENAME (FIELDNAME)

This does NOT affect the "user%" fields in the DMV (SYS.DM_DB_INDEX_USAGE_STATS).
(as you can see, still unseen)


Next step: view the histogram.

DBCC SHOW_STATISTICS (tablename, NCIDX_TABLENAME_FIELDNAME) with HISTOGRAM



So what's that show us?  That from 4/27 to 5/22 there were 4 rows, of which 1 was at 4/27 8:40.  It also shows there was one on 5/22 at 8:24.  And our indexes are still clean - so when we look again at historical usage of the table, it comes back clean.