Yesterday I got stuck in a scenario where I wanted to find out when the last record was inserted in the table. Well I had modify_date in sys.tables. BUT wait!! This is not the correct value. modify_date in sys.tables will give when the schema of the table was changed last time and not when any data was inserted.
Well we have last_user_update in sys.dm_db_index_usage_stats where we can find when the last data was inserted/updated/deleted. Whenever there is any insertion/update or deletion in the table, last_user_update in sys.dm_db_index_usage_stats is updated. There is a catch. sys.dm_db_index_usage_stats can be used irrespective there is any index on the table or not. Kool stuff..
SELECT object_name(object_id), last_user_update
WHERE object_id = object_id(‘TblName’)
Taking a deeper dive here. You can have any index key either clustered or nonclustered index on the table to fetch information from sys.dm_db_index_usage_stats. If you have clustered index then there would be only one entry for the table. If you have only one nonclustered index on the heap then you will find two entries. One for heap and another for update on nonclustered index pointer.
INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.
- SQL Server DBA “Interview Questions And Answers”
- SQL Azure Interview Questions and Answers Part – 1
- SQL Azure Interview Questions and Answers Part – 2
- Powershell Interview Questions and Answers
Online SQL Classes @ http://tuitionaffordable.webstarts.com