Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
2

I want to track how many times records are read in a specific table.

Let's say that we have this table

ID      Name
1       Cat
2       Dog
3       Fish

I want to know how many times the record "Cat" has been read and how many times the record "Dog" has been read and so on.

What is the best way to achive this goal?

share|improve this question
1  
Have you seen this SO question? – Jack Douglas♦ Aug 9 '11 at 18:21
Thanks but i'm looking for a kind of counter to track each time the record is access. The stored proc seems to be the best way but i'll will need to modify the application. What i really want is a way to do it via the database. I know that i can use database audit but is it too overkill. – Nico Aug 9 '11 at 19:21

2 Answers

up vote 6 down vote accepted

Write a stored procedure and make sure the data is only accessed through that procedure. In it, you can increment a counter in another table.

share|improve this answer
I would agree with Joshua and create a stored procedure that can be customized to your counter requirements. – StanleyJohns Aug 10 '11 at 4:48
Nico, modifying the application may be what you have to do. Is that going to be a big undertaking? – lowerkey Aug 10 '11 at 6:15
1  
So he needs to create a table that will hold a record for each other records in all the other tables? Isn't it an overkill? – Marian Aug 10 '11 at 7:30
Or, he could add another column to the existing table to store the access count. But then he'll have to consider the metaphysical problem of whether reading the access_count column should increment the table. I don't think having a table to hold record access counts is overkill. But then again, I don't know how many tables he's talking about. – lowerkey Aug 10 '11 at 10:40
I have only one table to track. The idea is to determine if the table is really needed. So i think that i will do a procedure. thx for your comments. – Nico Aug 10 '11 at 12:31
up vote 2 down vote

There is no way in SQL Server 2008 (at least one that I'm aware of) to know as much details as you want - to see how many times every single record has been read.

I can hardly see it as necessary, as long as every time a query goes to full table scan, than each table record will be read, even though it was not actually needed by a user, but only by a poorly written query.

PS: maybe I misunderstood your question, so, in this case, I'd go sit in a corner :-).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.