SQL index column always value from 1 to N
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)identity(1,1)Thanks
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