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 (https://blog.sqlauthority.com)
14 Comments. Leave new
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?
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.
Reading logs is not easy but try looking at the undocumented function fn_dblog
What about checking index?
DBCC IND () to determine the last page
DBCC PAGE () to determine last slot
Just a guess…
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).
Cdc with insert filtering don changes.
What about using %%physloc%% to derive a sql server alternative to oracle’s rowid?
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.
I tried this as well, and it seems to give me the results I wanted
Hi Dave,
How do I get the list of all the unique identifiers that were last inserted into a table? This unique identifier is the primary key of the table and is incremented sequentially for new records.
Basically, I am trying to write a trigger to update a column for all the latest inserted rows in a table.
Appreciate your help!
in trigger you can use deleted and inserted table to find what is inserted, deleted.
CREATE TABLE [dbo].[test_t](
[N1] [varchar](2) NULL,
[N2] [varchar](2) NULL
) ON [PRIMARY]
GO
;with cte as (
select a.*,row_number() over (order by a.col) id
from (select *,1 col from test_t)a
)
select b.* from (select top 10 * from cte order by id desc )b order by b.id
select * from table
except
select top(select (count(*)-1) from table) * from table