Is there any editable Auto-Increment besdies IDENTITY?

The reason I need this for is that I made a column on my table called display_order, for now it's smallint and the numbers were pre-determined.

However, when I insert a new record with my software I don't know how to get the highest number in that column and add 1, so I thought about the possibility of an auto-incremente开发者_如何学运维d column where if I change 8 to 9 it will change everything else accordingly.

Is this possible?

The answer to your question is "No" IDENTITY is the only auto incrementing capability (and these columns are not updatable)

But if this is a display_order field can't you just make it float to allow you to insert items between other items rather than having to shift all other items down to create a gap?

However, when I insert a new record with my software I don't know how to get the highest number in that column and add 1,

Insert MyTable( display_order, .... )
Select (
        Select Max(display_order) + 1
        From MyTable As T1
        ), ...
From MyTable

However, I wouldn't recommend this. If display_order is user settable, then I would simply assume relative values. Thus, it wouldn't matter if a user added two values with a display_order = 0. If you really want to go the extra mile and provide the ability to resequence the display_order, you could do it like so:

Update MyTable
Set display_order = Z.NewSeq
From    (
        Select PKCol
            , Row_Number() Over ( Order By display_order ) As NewSeq
        From MyTable
        ) As Z
    Join MyTable As T
        On T.PKCol = Z.PKCol

Because you only get one IDENTITY column per table, I would probably use a trigger or other mechanism (if there's a centralized insertion stored proc) to default it to one more than the highest number in the table if not provided. This avoids having to SET IDENTITY_INSERT or anything like that.