specifying an identity increment other than n+1?

Question

specifying an identity increment other than n+1?

Oblio Leitch

SSCrazy

Points: 2137
More actions
July 11, 2007 at 12:16 pm

#175223

Is there a way to specify an identity increment value other than n+1? Say, logarithmic?
Also, related, is there a way to do something like this:
DECLARE @example TABLE ([id] INT IDENTITY(0,1),
[num] AS (SELECT COUNT(*) FROM @example)
)
or do I have to use a trigger?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

Can you describe what you think your DECLARE is doing?

You can use increments other than +1 or -1, but I believe they need to be integer. You can check BOL for certainty - rather than take my word for it.

Incidentally, I read your DECLARE as recursive.

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 2

Yes, it is sort of recursive. In the example, every new row will have the number of rows currently in the database (might go up, might go down). Trouble is, SQL complains that subqueries are not allowed (phooey on them).

noeld SSC Guru Points: 96590 More actions · Answer 3

you can specify an Identity column and the a calculated column that uses that Identity value to execute a function that will return your logarithmic value.

Cheers,

* Noel

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 4

You're right; I found how to do that. However, is there any way to do the thing in my example -- introspectively get data from the table at the time of insert as part of the insert (or does it have to be done with a trigger)?

noeld SSC Guru Points: 96590 More actions · Answer 5

You can do it in a user defined function, not necessarily a trigger.

* Noel