locked
Alternate Identity Soltions RRS feed

  • Question

  • User977421240 posted

    I am wracking my brain trying to come up with a solid process for building something I'm going to call an Alternate Identity Column. An identity column is great except that when you delete a row it never 'heals'.  That is to say  if you have records 1, 2, 3, & 4 and remove record 3 and add another; your identity column would likely say something like 1, 2, 4, 5 after deleting record 3 and adding the new record. 

    My objective would be to be able to maintain an order either based on some sort criteria other than the 'alternate identity column' but ultimately the records that would be inserted into the table would be reordered in the AI column 1 to last record.  Has someone tackled this already? 

    I kinda have an idea about using three columns with a pseudo identity column (ID) that is incremented up +1 each row then next to it the Alt Identity Column which would be the same value as the ID column but have a zero tagged on to the value. (this would be so that you could insert record into any position in the order by tagging their alt identy value as #5 ie anything other than 0).

    So if you had 2, 3, 4 the alt identity column would show 20, 30, 40 for those rows and if we put in a new record between 3 & 4 it would have no value for the ID column and an alt identity of 35. so we'd now have 20, 30, 35, 40.  

    We'd immediately after the insert kick off a process that would do a sort based on the alt identity writing a new enumeration beginning with 1 of the ID column incrementing +1 each row. Once complete we'd do a sort based off the ID column writing a new enumeration beginning with 10 incrementing 10 each row to the end. 

    On completion we'd be ready for the next insert.

    Delete's would work the same way after the removal of a record.

    Has anyone done such a thing? Seen it? Pro's? Con's? Suggested alternatives? 

    I should tell you the objective is to have a simple means of moving up and down that ID column picking out records to view from that listing and not having to deal with the gaps that would be created over time. There are not that many records here to where this process would take that long to rewrite these columns nor will these records be removed or inserted into that frequently so this wouldn't be a bit performance issue.

    Thoughts?

    regards,

    Ken...

    Tuesday, May 10, 2016 4:00 PM

Answers

  • User753101303 posted

    Seems I miss something and I don't see what ypu call a nested update. For now to me a simple where clause would be enough to update the other rows that needs to be updated.

    Maybe close the thread, mature a bit your idea and come back later with a detailed description. For now my understanding was basically that you wanted to maintain a user defined position for each row so when a row is inserted you need to "push" later rows one position later before inserting the row and when you delete a row you need to decrement the position of later rows after having deleted it and that's pretty much all. I don't see at all why you would need to use joins and so on...

    Or do you handle rather a hierarchy? If what you need seems to match my earlier description, it should be easy to provide a quick sample so that you can see if it matches your own need.

    Once again my understanding is that you just want to be able to have a custom sort on a short list. Is this correct?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 12, 2016 1:41 PM

All replies

  • User753101303 posted

    Hi,

    Don't call it this way. It doesn't identify anything. It seems just to be a custom sort order. It seems basically correct ie :

    - if you insert you may have to update later rows if you have no room for the current row (or you could always rename if the update is quick enough)
    - when deleting you could renum but IMO this is not needed
    - if you really run in a case where a gap is a problem you could use https://msdn.microsoft.com/en-us/library/ms186734.aspx to renum the whole table

    But I'm not sure gaps should be a problem (IMO moving rows could be done by just exchanging their position, I don't think having no gaps is really needed). It is still a bit confusing as you refer here and there to the "ID column" but for now it looks like you just want to allow a  user to define a manual ordering ?

    Another thing I've done once is to sort on a SortOrder and I was using SortOrder,UserCode. I was using always the same high value by default but here the goal was more to allow to keep some rows at the top and then sorting the rest by name (something like VIP first and then the rest of the staff).

    Tuesday, May 10, 2016 5:33 PM
  • User475983607 posted

    You should not be concerned about gaps in an Identity field.  Having this concern usually means you have other issues.

    Anyway, I believe you can get around this using a CTE and SELECT ROW_NUMBER() OVER( ORDER BY [field]).

    USE [AdventureWorks2012]
    GO
    
    ;with cte(rowNum, LastName) 
    as
    (
        SELECT ROW_NUMBER() OVER( ORDER BY p.BusinessEntityID) as rowNum,
    		p.LastName
        FROM Person.Person as p
    )
    SELECT * FROM cte where rowNum between 10 and 15
    

    Results

    rowNum               LastName
    -------------------- --------------------------------------------------
    10                   Raheem
    11                   Cracium
    12                   D'Hers
    13                   Galvin
    14                   Sullivan
    15                   Salavaria

    Tuesday, May 10, 2016 6:34 PM
  • User977421240 posted

    Guys it isn't gaps that I'm so much concerned about as it is consistency and presentation. Part of this will be going into presenting data to the user and have sequential numbers helps the human to follow the flow better.  Think of this as sub index's in a larger file so if we had a table full of dogs and my sub index was based on pugs this number sequence would be for only the pugs the other dogs would not be affected by the sorts or renumbering that I wish to do or the inserts.  

    Two stored procedures will do pretty much all I need I think. The first one is an insert function that inserts a record with minimal information (in this case 2 columns will be populated the rest are all left unfilled). Then the second  Stored procedure will just handle the update with remuneration of the two columns then return control back to the calling source.

    I say calling source because the reason I'm breaking this into two separate SP's is when I remove a row I can simply fire the second SP and clean up the sub index neatly and when it returns all I will need to do is place the user on a viable document to continue their work (because the one they had been viewing will have been removed). So I will be getting dual purpose out of the code by breaking it apart.

    I almost have the second SP written but am having some issues with the sort part and getting that to work correctly.

    Is that making any more sense?

    Ken...

    Wednesday, May 11, 2016 8:25 PM
  • User753101303 posted

    It was basically my understanding. (ie you are using a SortOrder column whose purpose is to allow to define an arbitrary order for your list). To me it seems your approach is correct (ie when you insert or delete a row you may have to update rows with a higher or equal sort order (always if you never have gaps, less often if you do have gaps which could be a way to optimize that). For now it seems to me it should work.

    Do you have some kind of error message or is this that your SP doesn't do what you expect?

    Wednesday, May 11, 2016 9:05 PM
  • User977421240 posted

    Well as I am working through this I've evolved from the original question to building of the concept that I've settled in on I guess really. The challenge that I'm working on at the moment is building nested update with a select so that I can perform the ORDER BY then I should be good to go.  Been doing search for examples and found things close to that but nothing clear. Most are doing table joins.  Found one for Oracle but that will not help LOL.   I will keep hammering away at it I'll stumble on it sooner or later I'd guess.  Since there will seldom be more than 5 or 6 rows involve in the sub set it should be quick regardless.

    Ken...

    Thursday, May 12, 2016 1:26 PM
  • User753101303 posted

    Seems I miss something and I don't see what ypu call a nested update. For now to me a simple where clause would be enough to update the other rows that needs to be updated.

    Maybe close the thread, mature a bit your idea and come back later with a detailed description. For now my understanding was basically that you wanted to maintain a user defined position for each row so when a row is inserted you need to "push" later rows one position later before inserting the row and when you delete a row you need to decrement the position of later rows after having deleted it and that's pretty much all. I don't see at all why you would need to use joins and so on...

    Or do you handle rather a hierarchy? If what you need seems to match my earlier description, it should be easy to provide a quick sample so that you can see if it matches your own need.

    Once again my understanding is that you just want to be able to have a custom sort on a short list. Is this correct?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 12, 2016 1:41 PM
  • User977421240 posted

    I agree I am going to close this thread. I just need to open another one that is strictly focus on the issue of the nested select issue because that is really all that remains now. Once I get past this I have my solution.

    Thanks,

    Ken...

    Thursday, May 12, 2016 2:06 PM
  • User753101303 posted

    And try to better describe the final goal. For now my understanding was that you just wanted to allow a user defined sort (for example having a user being able to enter "Continent", "Country", "Region", "City" and to show that in the correct order or to add other level at the proper place etc...) but what you are actually doing seems more complex with the need for those "nested" statements. If this is a hierarchy you also have a type to handle that in SQL Server.

    In short it seems you'll need first to better explain what you are trying to do...

    Thursday, May 12, 2016 2:32 PM