How can I auto-increment a column without using IDENTITY?

Question

I'm creating a table with two columns that I want to auto-increment. One column is a primary key, so I'm using the IDENTITY keyword on it. The other column will be used to track the user-defined "sort order" of items in the table. Any time the user moves an item, its "sort order" will swap values with that of another element. However, when an item is inserted into the table, the inserted item should always be auto-assigned a sort-order value higher than any other value in the table. Here's a simplified version of the table creation script:

CREATE TABLE [AnswerRow] (
    [AnswerRowId] [int] IDENTITY(1,1) NOT NULL,
    [SortOrder] [int] NOT NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_AnswerRow_IsDeleted] DEFAULT 0,
    CONSTRAINT [PK_AnswerRow] PRIMARY KEY CLUSTERED ([AnswerRowId] asc)
)

What's the best way to make the SortOrder column auto-increment the same way the AnswerRowId column will (but still be able to modify sort-order values afterward)?

Answer 1

I'm not sure if this is what @Stephen Wrighton had in mind, but I think you could have an insert trigger make use of the IDENTITY value being generated for AnswerRowId:

CREATE TRIGGER [dbo].[AnswerRowInsertTrigger]
   ON  [dbo].[AnswerRow]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE a SET a.SortOrder = a.AnswerRowId
    FROM AnswerRow a JOIN inserted i ON a.AnswerRowId = i.AnswerRowId

END