October 10, 2008 at 11:53 am
Hello,
I'm working on a project (currently in modeling phase) that may require the use of a structure generically known in Oracle and UDB platforms as a "sequence number". Its similar to an Identity ID, but is not the primary key. Rather, its used as kind of a secondary index, that increments usually by 1, for each new occurrence of an attribute within a table. More often used in financial and transactional models, but basically works like an Identity insert only it increments for each occurrence.
1 1
2 2
3 1
4 1
5 3
where ID's 1,2, and 5 represent another occurrence. Hope that makes sense. What's best approach in SQL2005?
October 10, 2008 at 12:23 pm
Identity has nothing to do with primary key. They can be the same, but don't have to be.
What's an occurence? Not sure what you mean here.
October 10, 2008 at 3:29 pm
Hi Steve,
Sorry for any confusion. I agree IDENTITY has no relation to primary keys. I can't think of a simple explanation...let's say you have a CUSTOMERS entity that has a "Names" attribute, and an ADDRESS entity that has a "Street" attribute. We previously agree that 2 or more members of the CUSTOMERS entity can have the same "Street" address. In the CUSTOMERS entity we want to autoincrement and Identity type of field (but not the actual IDENTITY field which might be used as a surogate key or something (ID 1,,2 3, etc per row). But we want a extra field (Lets call it SEQ_NO) that is similar but increments per "occurrence" - so if the first entry John Doe lives at 'Address' 123 Sunnyvale Lane, SEQ_NO will seed at 1. Further down, say several rows, we see that Suzy Doe also lives at 123 Sunnyvale Lane, but that row's SEQ_NO value will be 2, not say, 10 which is what the IDENTITY field value is, because its only the second "occurrence of the address '123 Sunnyvale Lane'.
My apologies if I'm doing a poor job of describing this, but in other platforms you add a SEQUENCE NUMBER datatype or construct to handle this, in addition to whatever IDENTITY or other unique row identification method you might use. Does that help?
October 10, 2008 at 4:40 pm
October 10, 2008 at 4:45 pm
The only thing I can think of is to use row_number(). You can create a view that is used to select from the table that includes the row_number() calculation instead of storing the value in the database.
Example:
Select Id
,MyName
,row_number() Over(Partition By MyName Order By Id)
From dbo.MyTable;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply