How to put New record at the end of Database Table

[itslucky]

Dear Friends,

When i enter a new record in SQL Database from my C# application, the record stores in the database at random locations, but i want to make sure that every record must be enter at the end of the Table.

Actually i have to get the Last Balance of Customer, and it must be at the last record, but whenever i put a new record it stores at the middle or above, i need it to be store at the last so i get the last balance...

Plz help me out Dear Friends...

[kvprajapati]

>How to put New record at the end of Database Table.
A table is an unordered recordset. The position of a record in the table is not relevant unless you impose an order on it.

If your table has transaction date column then use SELECT statement with ORDER BY clause.

str="select * from tableName order by transactiondate"

[sknake]

The physical order of records written to an SQL table are determined by the clustered index if one is present. If one isn't present then they are just added in the order they are inserted and likewise they are returned in the order they are selected unless you use an order by clause.

You should add a "CreateDate" column or an auto incrementing record id column to determine which record is the last record. By default an autoinc primary key column creates the clustered index unless you specify to override it.

IE default value and clustered:

CREATE TABLE Test
(
  RecordId int identity(1000, 1) PRIMARY KEY
)

A nonclustered version:

CREATE TABLE Test
(
  RecordId int identity(1000, 1) PRIMARY KEY NONCLUSTERED
)

[edit]
Also if someone changes a clustered index that you are relying on to determine order than you cannot figure out by a column value then you run the risk of ruining your database by changing an index. This is a bad idea.... add another way to determine record sequence.
[/edit]

[jpattison]

...You should add a "CreateDate" column or an auto incrementing record id column to determine which record is the last record. By default an autoinc primary key column creates the clustered index unless you specify to override it.

[/edit]

After creating your table with the Create Date - or with the RecordID from Sknake above... then use Max(<fieldname>) to bring back the last entry.