March 12, 2004 at 10:07 am
I want to create a table that has an identity key that rotates once the top-end of a range is reached. In other words, insert 0-99, then on next insert, clear 0 and insert there, then 1, etc.
Any suggestions on how to make this happen in SQL7?
TIA
March 12, 2004 at 1:15 pm
What about using a real identity column with a computed column for your "identity" column?
create table testid(
id int identity(0,1),
fakeid as id%100)
--Jonathan
March 12, 2004 at 2:33 pm
Wow, that's really cool. It's pretty close to what I need, but I think there might be some problems. First, it's going to be used by a "human" - and if a record is deleted, it'll create a gap in the sequence which the human won't understand (or would expect to be able to fill by hand). Second, it's going to be used by a search - if I try to lookup a current number that's been deleted, it might show me a past number.
Can you suggest a way that the system can "clean up" after itself? Maybe a trigger before insert that will purge old fakeids?
Thanks!
March 12, 2004 at 3:43 pm
Do you mean that you also want to ensure that there are no more than 100 rows in the table? And somehow re-use the numbers that were assigned to randomly deleted rows? These characteristics have nothing to do with the identity property. If numbers are reassigned to different relations, then how could one possibly support any lookups by the number? As your subject line references FILO (First In Last Out), does this mean that any subsequent rows would be renumbered after any are deleted, i.e. the rows would always be consecutively numbered from oldest to newest, and the oldest would be automagically deleted if another row is inserted when there already exist 100 rows, forcing all rows to be renumbered?
--Jonathan
March 15, 2004 at 8:42 am
Well, you're right. I didn't explain or think it through. I need to track & archive the data, so I guess I can't reuse the key. What if I build a calculated field (using your suggestion) off of an identity field, but add an "insert date" and make that and the calculated field the primary key. That way I can use the identity field for internal reference, but never duplicate the number/date pair. I would also be able to select the most recent set of fakes - "top 100 order by date".
Any advice or holes in my logic?
Thanks
March 15, 2004 at 9:05 am
Well, you of course mean "TOP 100...ORDER BY DATE DESC". But what happens when you've got 150 rows and someone deletes rows 50-99? Your TOP query would then return 50 pairs of "duplicate" fakeids...
Also, what if more than 100 rows are inserted within the same transaction? That would error because of duplicate primary keys...
--Jonathan
March 15, 2004 at 2:04 pm
Right, I think the current system will prevent the users from deleting records (I need to have history that relates to the records anyway). And, they won't be able to enter more than one at a time.
Actually, the issue I ran into is I can't create a primary key on a calculated field. I can create a trigger, though.
What do you think?
March 15, 2004 at 2:09 pm
I can't create a primary key on a calculated field
Yes, you can:
CREATE TABLE TestId(
Id int IDENTITY(0,1) NOT NULL,
FakeId AS ISNULL(Id%100,-1),
Dat datetime NOT NULL DEFAULT GETDATE(),
PRIMARY KEY(FakeId,Dat))
--Jonathan
March 16, 2004 at 11:18 am
I get the error "Column name 'FakeId' does not exist in the target table." I'm working in v7, does that cause the issue?
March 16, 2004 at 11:21 am
Yes. You need to upgrade to SQL Server 2000. SQL Server 7 did not allow computed columns in keys.
--Jonathan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply