How to know the insertion order of records in a table?

Question

How to know the insertion order of records in a table?

vinod.km

SSC Eights!

Points: 971
More actions
September 6, 2010 at 1:50 am

#237955

Hi All,
I have an existing table, which has thousands of records.There is no identity column in this table & I can not modify the existing table structure also. The table has a primary key(Admission Number).In this table, for a single admission number (say adm123), there are 5 records. Now, I have to know that, out of these 5 records which record was inserted first in the table. In other words, I want to know the insertion order of records in which records gets inserted into a table. (Server is SQL Server 2000)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Without any auto-incrementing column or a datetime column with default=getdate() it's almost impossible to say. If data were inserted manually, there might be a chance to either echeck transaction logs (most probably using some addtl. tools to make that log readable) or to try point in time recovery and narrow down the time frame data were inserted.

If the data were loaded using a batch process, you should try to find the source data and see if you can get any information based on the source file and the import process (maybe there's an ORDER BY somewhere in the process).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pandeharsh Ten Centuries Points: 1106 More actions · Answer 2

Can't you create one additional table,which will hv its own id(ideneity,id of that table,Createdate (datetime).

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

vinod.km (9/6/2010)
Hi All,
I have an existing table, which has thousands of records.There is no identity column in this table & I can not modify the existing table structure also. The table has a primary key(Admission Number).In this table, for a single admission number (say adm123), there are 5 records. Now, I have to know that, out of these 5 records which record was inserted first in the table. In other words, I want to know the insertion order of records in which records gets inserted into a table. (Server is SQL Server 2000)

You could experiment with the hidden row identifier %%LockRes%% If %%LockRes%% is appearing as a 3 component field such as 1:127:15 then you could try the query below. Although I am not sure how reliable this is, there may be some scope in this approach. What do you think?

SELECT %%LockRes%%, *

FROM YourTable

ORDER BY

CONVERT(INT, LEFT(%%LockRes%%, CHARINDEX(':', %%LockRes%%) - 1)),

CONVERT(INT, SUBSTRING(%%LockRes%%, CHARINDEX(':', %%LockRes%%) + 1, CHARINDEX(':', %%LockRes%%, CHARINDEX(':', %%LockRes%%) + 1) - CHARINDEX(':', %%LockRes%%) - 1)),

CONVERT(INT, RIGHT(%%LockRes%%, CHARINDEX(':', REVERSE(%%LockRes%%)) - 1))

Lowell SSC Guru Points: 323518 More actions · Answer 4

i think the 2005 %%LockRes%% row identifier and the 2008's equivalent %%physloc%% only tell you how the data is stored, which is based in the clustered index of the table (if it exists); if it was a heap table, you could infer the insertion order i think.

as i remember it, the format is partitionid:pageid:rowId, so if you were to change the clustered index or a reindexing occurred, you'd see changes in the value returned.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve block SSCertifiable Points: 7698 More actions · Answer 5

Admission Number must not be a true primary key if you can have multiple instance of it. Is there another column combined with Admission Number that provides the complete primary key? If so, it might provide some clues as to which one is older or newer.

I would guess that you don't have a column that stores a date or the date does not provide helpful information. Perhaps there is some other kind of status column that might provide some information where you can infer the row you want.

Without an identify column or some other kind of information, there is no way I know of that you can know the order that they were inserted into the database, especially with SS 2000.

A companion table with a true primary key of your table and an identify or date/time column might be a reasonable way to deal with this in the future.

Steve