SQL SERVER – Retrieve Last Inserted Rows from Table – Question with No Answer

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)

About these ads

10 thoughts on “SQL SERVER – Retrieve Last Inserted Rows from Table – Question with No Answer

  1. Pinal: how about using CDC for the tables for which was wanted to track the changes in terms of DML? I think that should give us fair idea about such activities however this will not record the data which had been already inserted/deleted or updated but will take care of forth-coming data. What do you think?

  2. I think this can be achieved by writing query in this format. I’m not a techie to Write the code though. Here is my thought.

    1. Get the last element using last() function with a specific column of the table and get row id for the same.
    2. Once you have the row id. You can loop through the Row ID – X (bottom 10) to Row ID. I guess this should be the possible solution with using any third party tool.

  3. What about checking index?
    DBCC IND () to determine the last page
    DBCC PAGE () to determine last slot

    Just a guess…

  4. My question would be if there are any indexes on the table? If not, then the data is being stored in the order in which it was entered. You could then insert all of that data into a temp table that has an identity column and it will order them in the way that they were inserted.

    If they have indexes on the table I would say they are out of luck. Moving forward, add a column or trigger to track it. Im not sure reading the logs is going to get you where you need to be….

    • Late response, but would adding an int as an identity field do the trick? when you set “is identity” to yes in the options (table designer, identity specification, is identity) in the design view in SSMS, it fills the int field with unique ints for use as an ID/primary key.

      This would then provide something to order by (incidentally, this is great when you have two duplicate rows in SQL server that refuse to be deleted as doing something to one affects both – this solution adds a new field so that each row would have a unique number, thus safely differentiating them and allowing one duplicate record to be deleted).

  5. Pingback: SQL SERVER – Q and A from Facebook Page | Journey to SQL Authority with Pinal Dave

  6. SELECT TOP 10 name,productid, ROW_NUMBER() OVER (ORDER BY GETDATE()) ord
    FROM AdventureWorks2008R2.[Production].[Product] WITH (INDEX(0))
    ORDER BY ord DESC

    I have tried this query on few tables with/without index/pk, its working fine but not sure for all cases.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s