1

I want to implement a feature that will show users how many times a record has been viewed (been SQL selected), but don't know what is the best way to do it. The only way I can think of right now is to get the all recordID that has been SELECTED and then use xml bulk update to update the view count. Is there a better way or table design to do the view count? Thanks!

---------------
| recordID    |
| recordValue |
| viewCount   |         
---------------
Vincent Liao
  • 147
  • 1
  • 10
  • 1
    I envision some sort of combination of a view, a trigger and/or a stored procedure. That said, unless you're trying to do some sort of code instrumentation, "number of views" is normally a part of the *business domain;* that is, you don't necessarily want to know how many times a table record has been accessed, but rather how many times an invoice (or some other business object) has been accessed, and by whom. – Robert Harvey Jul 09 '15 at 17:04
  • Thanks for answering, but the goal is to know how many times a table record has been accessed and I'm wondering is there a better table design to do it? – Vincent Liao Jul 09 '15 at 17:12
  • Something like this maybe? http://stackoverflow.com/questions/2155594/how-can-i-tell-if-a-database-table-is-being-accessed-anymore-want-something-lik – Rick S Jul 09 '15 at 17:26
  • A better... *table design?* No, a table is a table. You would need something else. – Robert Harvey Jul 09 '15 at 17:37

1 Answers1

1

I don't think this level of auditing is possible. However, you can get a list of the commands from the last 24 hours using the query below. Perhaps you can run this on a regular basis with some good string parsing functionality added. 

SELECT 
    QueryDate=execquery.last_execution_time,
    SQLString=execsql.text,
    QueryCount=execution_count
FROM 
    sys.dm_exec_query_stats AS execquery
    CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY 
    execquery.last_execution_time DESC
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • If it is possible, it would have to be done by requiring users to access the table through some mechanism like a stored procedure or a view, perhaps in concert with a trigger of some sort. You could then do whatever level of auditing you wanted. – Robert Harvey Jul 09 '15 at 17:36
  • That is a good point. If add-hoc queries do not need to be audited then the way you described is sound, moreover, this is, in my opinion, the way applications should access the data. The only issue with that approach is the baggage that would be carried forward with each new reporting stored procedure and/or view created. – Ross Bush Jul 09 '15 at 18:12
  • Thanks! I'll try this! – Vincent Liao Jul 10 '15 at 21:52