creating reusable primary keys

I guess this is more a DB question, but I'm thinking there is some wisdom on how to create PKs in CF that I don't have. I am being space conscious and have declared a PK for a table with a data type of TINYINT because I will never have more than 255 records. My records don't have suitable unique values so I wanted to use the IDENTITY attribute in SQL Svr to automatically create the primary keys. Trouble is that once I delete one of the records, that PK value will never be used again. Is there a clever way in CF to create the PK yourself and be able to reuse the value when it is deleted. Nick Cabell 451 Learning Systems (650) 823-1858

Yes, don't delete the records -- just flag them as deleted (have a column in the db to indicate the the record is deleted) Dick On May 31, 2004, at 4:53 PM, Nick Cabell wrote: ----- Excess quoted text cut - see Original Post for more -----

That would defeat the original purpose of saving space in the DB. Besides, integers aren't that big and space is cheap these days. Why go through the trouble of doing all that to save less than 1kb? Anyone remembers win2k? How did it happen - some space saving issue, wasn't it? [Tom Kitta] My philosophy is to make things as simple as possible (but not simpler) if at the same time I can get some free goodies like performance or extra free space than I go for it. But KISS rules most of the time, if not all of the time. Just my .02c. TK http://www.tomkitta.com   Yes, don't delete the records -- just flag them as deleted (have a   column in the db to indicate the the record is deleted)   Dick   On May 31, 2004, at 4:53 PM, Nick Cabell wrote:   > I guess this is more a DB question, but I'm thinking there is some   >  wisdom on how to create PKs in CF that I don't have.   >   >  I am being space conscious and have declared a PK for a table   >  with a data type of TINYINT because I will never have more than   >  255 records. My records don't have suitable unique values so I wanted   > to   >  use the IDENTITY attribute in SQL Svr to automatically create the   >  primary keys. Trouble is that once I delete one of the records,   >  that PK value will never be used again.   >   >  Is there a clever way in CF to create the PK yourself and be   >  able to reuse the value when it is deleted.   >   >  Nick Cabell   >  451 Learning Systems   >  (650) 823-1858   >

I dont believe he meant flag it and leave it... what would be the point of that... he meant flag it then the next time you add a record just UPDATE a field flagged as deleted... (which is probably the only way to do what he wants easily... prepopulate 255 records and flag them all deleted to start out) I usually set it to a normal int and PK then use MAX(pk) to add 1 to and get the new number if I want reusable numbers your not going to be able to keep it in the ranges of 1 - 255. eventually its going to need higher numbers unless you waste a lot of time and server processing to figure out what numbers are available in that range, 1 or 2 k isnt going to kill you when you said yourself.... your never going to have more than 255 records in it anyway   That would defeat the original purpose of saving space in the DB. Besides,   integers aren't that big and space is cheap these days. Why go through the   trouble of doing all that to save less than 1kb? Anyone remembers win2k? How   did it happen - some space saving issue, wasn't it?   [Tom Kitta]   My philosophy is to make things as simple as possible (but not simpler) if   at the same time I can get some free goodies like performance or extra free   space than I go for it. But KISS rules most of the time, if not all of the   time. Just my .02c.   TK   http://www.tomkitta.com     Yes, don't delete the records -- just flag them as deleted (have a     column in the db to indicate the the record is deleted)     Dick     On May 31, 2004, at 4:53 PM, Nick Cabell wrote:     > I guess this is more a DB question, but I'm thinking there is some     >  wisdom on how to create PKs in CF that I don't have.     >     >  I am being space conscious and have declared a PK for a table     >  with a data type of TINYINT because I will never have more than     >  255 records. My records don't have suitable unique values so I wanted     > to     >  use the IDENTITY attribute in SQL Svr to automatically create the     >  primary keys. Trouble is that once I delete one of the records,     >  that PK value will never be used again.     >     >  Is there a clever way in CF to create the PK yourself and be     >  able to reuse the value when it is deleted.     >     >  Nick Cabell     >  451 Learning Systems     >  (650) 823-1858     >

The difference between a tinyint (1 byte?) and a char(35) (35 bytes) on a table that will never contain more than 255 records is pretty minimal.   8k, maybe.  Is it really that important to save 8k of space? Just use a char(35) and use the CF CreateUUID function.   - Rick

>I guess this is more a DB question, but I'm thinking there is some ... > >Nick Cabell First of all, let me say, I agree with the most people's opinion "minor space gain with quite a bit of effort may not be worthy".  Now, on the technical feasibility of the problem, I believe yes, here's a starter, use UDF, then, map it into Table DDL, say, table name, tmp, -- the UDF may look like this create function keyVal() returns int AS begin   declare @intID as int   if exists (select * from tmp)    select @intID = (select Min(keyCol) + 1 from tmp)   else    select @intID = 1 RETURN @intID end On a similar note, >[Tom Kitta] >My philosophy is to make things as simple as possible (but not simpler) I fully agree.

----- Excess quoted text cut - see Original Post for more ----- I would strongly recommend that you simply use a regular INT column that will let you use IDENTITY values normally. Your desire to be "space-conscious" will cost you more than it's worth. In addition, I would strongly recommend that you not attempt to reuse surrogate primary key values. This may add significant complexity to the database, and won't provide any benefit. Can you explain what exactly you're concerned about, with regard to space? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444