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

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)?

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

Two ways come to mind right off, the first is a trigger, which is not what I'd do personally.

The second, would be to have my sql do something along these lines:

INSERT INTO AnswerRow(SortOrder, IsDeleted, Answer)
SELECT MAX(SortOrder) + 1, 0, 'My New Answer'
FROM AnswerRow

For what it's worth, I've found it much easier to use a floating-point column for my sort position than to use an integer value. For new records, you would still need a trigger that sets the value equal to the largest existing value plus some constant (say 100). For updating the sort position of an item, though, you can simply find the sort position of the entry before and the entry after the entry you are moving. Average those two values and you have your new sort position.

DECLARE @NextItem int

SET @NextItem =    (SELECT 
       COUNT(*) + 1 AS NewSortOrder
    FROM 
       MyTable 
    WHERE 
       AnswerRowID=@AnswerRowID 
    ORDER BY 
       SortOrder ASC)


   --now you can use @NextItem
   INSERT INTO AnswerRow(SortOrder, IsDeleted) VALUES(@NextItem, 0)

Or you can simply use the current order:

 SELECT TOP 1 (ISNULL(SortOrder, 0) + 1) as NewSortOrder FROM AnswerRow WHERE AnswerRowID=@AnswerRowID ORDER BY SortOrder DESC

You might want to check if this returns a NULL in case no records exist, check IsNULL in BOL.

Either way the point is you want to get the current number, add one to it, and then use that value to do your insert.