Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Ordering Rows for Contents

    I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents,
    However I know this will get quite messy at the application level.

    I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess?

    How can I set an on insert constraint, or what its called for this in SQL Server?

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Identify it in the DDL as an integer Identity column. Specify seed and increment amount.

    The DB will assign the next higher number, guarenteed to be unique.

    Caution; it may on occassion skip a number, but the highest ID will always be the most recently added.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    for details see IDENTITY in BOL.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE TABLE....

    , Add_DT datetime DEFAULT(GetDate())


    SELECT * FROM ....

    ORDER BY Add_DT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    The rest of the data alreay has a PK, and some FKs, but can I use the same process but make it not identity?

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Nate1
    The rest of the data alreay has a PK, and some FKs, but can I use the same process but make it not identity?
    See MSDN on Identity columns here. Good to read since there can be gotchas depending on needs and environment (eg: is it a replicated DB, etc).

    It doesn't have to be the PK, but it's probably advisable to index it.

    Note on using a time stamp rather than an ID: On mass Inserts (and depending on the version of SQL) getdate() may be executed once (all rows identical) or once for each row - so unique values is not a guarantee. Does that matter for your application? If not; maybe a simple TimeStamp column that has default of "getdate()" is best (at the expense of 8 bytes per). Timestamps can come in handy for other reporting needs. For that matter; use smalldatetime (4 bytes). Having an unique ID can be great for linking or if MS Access is in the mix, since it chokes without a unique key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •