| Author |
Topic |
|
daxfohl
Starting Member
5 Posts |
 Posted - 2009-08-28 : 12:27:38
|
I want to know if it's possible to make an an autoincrement variable without having a table. Basically my problem is that I want to have something like the following Animals table:ID Name Type IdWithinType1 Rex Dog 12 Shiva Cat 13 Ubu Dog 24 Spot Dog 35 Angel Cat 2 etc. (Of course I'd replace Type with a numeric TypeId in the end product). But the goal is to be able to not only give each animal a unique ID, but also to give it an incrementing ID within its type. ie, there should be Dog #1, Dog #2, Dog #3, ..., and Cat #1, Cat #2, Cat #3, .... I need to keep track of both the global id and the idWithinType to integrate with existing systems. What's the easiest way to do this? All I've figured out is to create additonal Dog and Cat tables that have nothing but an autoincrementing ID field (and a dummy field so that there's something to insert) and do a (pseudocode below)INSERT INTO dog(dummy) VALUES(0);int idWithinType = SELECT @@IDENTITY;INSERT INTO animal(name, type, id_within_type) VALUES("Rex", "dog", idWithinType);Is there a better way to do this? Seems like a hack, not to mention slower than necessary. Does SQL Server have some way of just creating global autoincrementing counter variables without creating extra tables? I could swear I've done something like this before like,CREATE AUTOINCREMENT_COUNTER dog_id_counter; and then was able to simply do the above in one statement like:INSERT INTO animal(name, type, id_within_type) VALUES("Rex", "dog", SELECT FROM dog_id_counter);However that was years and years ago, and I have no idea which database it was in or even whether I was dreaming it. But does SQL Server have some kind of functionality like that? Or, is there any better way of doing what I'm trying to do?Thanks for your help. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-08-28 : 12:38:53
|
| If you're using SQL 2005:SELECT ID,Name,Type ,[IdWithinType] = ROW_NUMBER() OVER(Partition by type order by id)FROM yourTableORDER BY IDJim |
 |
|
|
daxfohl
Starting Member
5 Posts |
Posted - 2009-08-28 : 12:55:04
|
| Hmm, interesting idea to keep in my back pocket, but that wouldn't maintain consistency after deleting something in the middle or moving to an archive table etc, right? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-08-28 : 13:12:02
|
| Right, this will jsut count the table you're working on now. I'm afraid I'm out of solutions for you. Sorry. One of the smart people on this forum may still have an answer for you, though.Jim |
|
|
|
daxfohl
Starting Member
5 Posts |
Posted - 2009-08-28 : 13:18:37
|
| Aha, apparently SELECT seq.nextval FROM dual in Oracle is what I was remembering, and the standard name for the feature is "sequences". AFAICT there's nothing similar in SQL Server (why?). Anyway, here's a pretty good article on two ways to replicate that functionality - one simple and one fast. The comment about DEFAULT VALUES also shows how to get rid of the "dummy" field I had mentioned.http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-08-28 : 13:22:42
|
| can you work with a function which accepts Name and Type and returns IdWithinType by selecting from this table or suggests the next increment if it cannot find it in the table? |
|
|
|
daxfohl
Starting Member
5 Posts |
Posted - 2009-08-28 : 13:35:27
|
| rohitkumar, that's what I did in the first version of the app, but switched to the autoincrement to avoid race conditions. ie, if two people ran the function at the same time, they would both receive the same idWithinType value, which would cause duplicate values after inserting. (sure you could check for the duplicate values and rollback or whatever, but may as well just use a sequence and avoid it completely). If you've got an idea that would avoid the race condition issues, I'm open. |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-08-28 : 13:47:58
|
| have a table which stores Name, Type, IdWithinType mapping. then have a function to insert new mapping and return the new (incremented) IdWithinType. Here even if two people call this function at the same time they will get different id's |
|
|
|
daxfohl
Starting Member
5 Posts |
Posted - 2009-08-28 : 13:55:53
|
| How is that different from what is being done in Option 1 in the link I gave? |
|
|
|
|
incrementing without extra tables?