SQL index column always value from 1 to N

Question

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,

    OutputSentence nvarchar(500),

    IsPrimaryOutput bit DEFAULT 0,



    PRIMARY KEY(ID),

    FOREIGN KEY(SpeechConfigCode) 

        REFERENCES SpeechConfig 

        ON UPDATE CASCADE ON DELETE CASCADE

);

I would like to add an index column that increases automatically (not

identity(1,1)

) which always has values from 1 to N (according to the number of rows).

identity(1,1)

will not do since there are many cases there are no continues numbers from 1 to N because it's intended for primary key.

Thanks

Answer 1

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

The issue of primary:

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

SQL index column always value from 1 to N

The issue of primary:

Sign up