I think my question is very simple but every search in the web shows me results about SQL indexing.
I use the following SQL query to create a simple table:
CREATE TABLE SpeechOutputList
ID int NOT NULL IDENTITY(1,1),
SpeechConfigCode nvarchar(36) NOT NULL,
IsPrimaryOutput bit DEFAULT 0,
ON UPDATE CASCADE ON DELETE CASCADE
Trying to keep such an index field sequential, and without gaps, will not be efficient. If for instance a record is removed, you would need to have a trigger that renumbers the records that follow. This will take not only extra time, it will also reduce concurrency.
Furthermore, that index will not be a stable key for a record. If a client would get the index value of a record, and then later would try to locate it again by that index, it might well get a different record as a result.
If you still believe such an index is useful, I would suggest to create a view that will add this index on-the-fly:
CREATE VIEW SpeechOutputListEx AS SELECT ID, SpeechConfigCode, OutputSentence, IsPrimaryOutput, ROW_NUMBER() OVER (ORDER BY ID ASC) AS idx FROM SpeechOutputList
This will make it possible to do selections, like:
SELECT * FROM SpeechOutputListEx WHERE idx = 5
To make an update, with a condition on the index, you would take the join with the view:
UPDATE s SET OutputSentence = 'sentence' FROM SpeechOutputList s INNER JOIN SpeechOutputListEx se ON s.ID = se.ID WHERE idx = 5
You explained in comments that the order should indicate whether a sentence is primary.
For that purpose you don't need the view. You could add a column
idx, that would allow gaps. Then just let the user determine the value of the
idx column. Even if negative, that would not be an issue. You would select in order of
idx value and so get the primary sentence first.
If a sentence would have to be made primary, you could issue this update:
update SpeechOutputList set idx = (select min(idx) - 1 from SpeechOutputList) where id = 123