Member Avatar for 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...

  • 4 Contributors
  • 3 Replies
  • 207 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by jpattison
Member Avatar for 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"
Member Avatar for 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]

Edited by sknake because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.