none
SQL Insert sequence RRS feed

  • Question

  • Hello,

     

    I setup a TableAdapter on a Table, and I am Inserting records into the table.  (See code below).  I would like to have the records shown in the order they are added which means I would like the records appended, or not in a sorted order.  When I look at the table the records are not in the sequence they were added.  How can this be fixed?

     

    Code Snippet

    Try

       retValue = Me.SampleReportTableAdapter.Insert(f(0), f(1), f(2), f(3), f(4), f(5))

    Catch ex As Exception

       ErrorExit(ex)

    End Try

     

    I am using SQL 2005 Express.  I am wondering if that is the problem.

     Thanks,

    Tom

     

    Thursday, June 21, 2007 1:52 PM

Answers

  • Generally speaking, you should assume that unless you include a ORDER BY clause in your query, SQL Server will return rows in an arbitrary order.  If the table doesn't include a column that you can infer the insert order from - an identity column, for instance, or a column that defaults to GETDATE() - you're not going to be able to reliably get rows out in the order you put them in.

    It's usually better to make the primary key of a table a synthetic key like an identity than a natural key like an SSN anyway.  The fact that it gives you the insertion order is a bonus.
    Friday, June 22, 2007 12:51 AM

All replies

  • Generally speaking, you should assume that unless you include a ORDER BY clause in your query, SQL Server will return rows in an arbitrary order.  If the table doesn't include a column that you can infer the insert order from - an identity column, for instance, or a column that defaults to GETDATE() - you're not going to be able to reliably get rows out in the order you put them in.

    It's usually better to make the primary key of a table a synthetic key like an identity than a natural key like an SSN anyway.  The fact that it gives you the insertion order is a bonus.
    Friday, June 22, 2007 12:51 AM
  • Hi Robert,

     

    Thanks for taking time to reply to this question.  I will take your advice and add a key to the table.

     

    Thanks

    Tom

     

    Friday, June 22, 2007 1:51 AM