Suppose I have table with the following structure -

Create table TblInfo(Name varchar(20), Email varchar(30))

Note that I have not put any constraints or setup any relationship on this table. No index No primary key.

Now I insert 5 records into this table

insert into TblInfo('abc', 'abc@dbforums.com')
insert into TblInfo('def', 'def@dbforums.com')
insert into TblInfo('ijk', 'ijk@dbforums.com')
insert into TblInfo('lmn', 'lmn@dbforums.com')
insert into TblInfo('opq', 'opq@dbforums.com')

What will be the sql query to retrieve Last 3 inserted records?

Above is just a scenario I created for illustrating the problem. The generic question I have is - How to retrieve Last n inserted records from an un-indexed table (having no primary key or index)?

adonisaseem, I think you have the wrong idea about how a relational database works. When rows are added to a table, they can be put on any page in any order.

With the design of your table, there is no way to know when rows were added or in what order. Even if you did a Select over a multi-row insert, all the Inserted rows would be displayed and not just the last 3 (assuming more than 3 rows were Inserted at one time).

Since neither Name nor Email address have anything to do with Insert order, Indexes won't help any.

What you need is something that does keep track of the Insert order. This can be a Timestamp column or an Identity column (or even a column that you increment manually).

Once you have this, something like would retrieve the last 3 rows entered:

SELECT TOP 3 column-list
from table-name
ORDER BY Timestamp or Identity column DESC

Identity columns do not determine order of insertion.

Thanks Stealth_DBA for reply. I know by using identity field or timestamp we can get the desired result. I was wondering if sql server internally maintains such information. so you say it does not. I know oracle has something called rowid, can it be used for finding last entries without using identity or timestamp in oracle? Any idea?

SQL Server records information about the insertion order in the log. Using the Change Data Capture feature it is possible to retrieve that information
Change Data Capture

Oracle has a similar feature called Flashback.

Oracle's ROWID doesn't necessarily retain the insertion order. As George has already said, neither does IDENTITY in SQL Server.

Correct me if I am wrong -

There is no sql query which can show me last n rows inserted in a table like this - Create table TblInfo(Name varchar(20), Email varchar(30))
without altering it

Assuming you have CDC enabled you could do it like this (untested code):

SELECT TOP (@n) Name, Email,
sys.fn_cdc_map_lsn_to_time(__$start_lsn) CreationDate
FROM cdc.dbo_TblInfo_CT
WHERE __$operation = 2
ORDER BY CreationDate DESC;

How's about renaming the table, adding an extra datetime column, then creating a view on the changed table with the orignal name, which only contains those two columns?

I've had to perform similar fudges before to make a change to an existing application without the FE "knowing".