Some conversations are very interesting and I would like to bring them to everyone’s attention. Here is one of the conversation happened earlier this week. Let us say the question was asked by Dustin.
Dustin: Can we retrieve last 10 rows from a table?
Pinal: Sure, just get the top rows from the end of the table. For this you will have to order table with Identity Column Descending.
Dustin: I do not have an identity column in table.
Pinal: No problem. Do you have a column which tracks the date and time for the newly inserted row or timestamp?
Dustin: No such thing in my table.
Pinal: Hmmm… That means there is no property which can identify if the rows were inserted last or first.
Dustin: I believe so. Any other thoughts?
Pinal: Well, is there any auditing on the table or any trigger or any other mechanism where you store your data to another table for example with an OUTPUT clause?
Dustin: No, nothing like that. Is there any administrative ways to get the last inserted row?
Pinal: What do you mean?
Dustin: Can I read the logs and know what were the rows inserted?
Pinal: I have personally never tried to read log files and I have no clue how to do that. If you are going do that, I suggest you take backup of your log file and do the experiment on it. There are good chances if you use tool which is not safe you will get your database at very high risk.
Dustin: Do you recommend any tool?
Pinal: No, I do not recommend any tool as of now. If I will find one, I will write a blog post.
Note: Please do not post a comment with third party tools link, I will delete them without notice. I will be doing my research soon and will post a blog about it.
Reference: Pinal Dave (http://blog.sqlauthority.com)