Hi
This is more of a theoretical discussion on how to handle a problem.
Here's the issue: We have many tables that are being populated from multiple sources, at one point the "PK" of "TableXYZ" needed to contain alphanumeric characters. So they (original developers) created the PK field as a varchar. This was seen as a good solution because, at the time, the info was coming from another system and was being inserted and not auto generated like if it was a numeric identity field.
Time passed and they realised they don't need varchars so they started using numerical values. PRoblem is the field is STILL a varchar. More time passes and the developers lose/forget how they are generating these ID's. Searches through existing code is turning up nothing helpful.
So we, the new developers, need to clean this all up and do it right.
Suggestions from some developers on how it's currently generating the Id's is doing a "select max(id)+ 1 from TableXYZ". Now first off I don't beleive them becuase you can't explicitly/implicitly convert a varchar to an int. I also hate this model because it's wrong. I thought maybe they had a table that contorls the 'next id' of each other table and uses a stored proc wrapped in a TX to 'get the next id' but I can find no evidence of this AND some old, non working code, actually have "SELECT max(id) + 1 from TableXYZ. (Rolling my eyes at this.)
So I would like to hear how some of you would deal with this problem taking into account the following:
1- Doing it right is more important than doing it fast.
2- We don't understand that full 'scope' of all the data relations as the data structures are undocumented and related across multiple db's.
3- We can't bring these systems down for long periods of time and data is being inserted almost constantly.
Off the top of my head I came up with the following:
- ADD an IDENTITY field to TableXYZ (auto populated). Start the 'seed' at a number much greater than the current max value in the table. (To compensate for the growing data while we work on it).
- Add an insert trigger that will take the @@IDENTITY and store it in the current "ID".
Now I'm thinking 'Voila.. instant solution'. This allows us to use the @@IDENTITY variables to create all our relations, ensures old relations aren't broken (as we won't touch the old ID') and it has a minimal impact on the current architecture.
Any thoughts?
This is more of a theoretical discussion on how to handle a problem.
Here's the issue: We have many tables that are being populated from multiple sources, at one point the "PK" of "TableXYZ" needed to contain alphanumeric characters. So they (original developers) created the PK field as a varchar. This was seen as a good solution because, at the time, the info was coming from another system and was being inserted and not auto generated like if it was a numeric identity field.
Time passed and they realised they don't need varchars so they started using numerical values. PRoblem is the field is STILL a varchar. More time passes and the developers lose/forget how they are generating these ID's. Searches through existing code is turning up nothing helpful.
So we, the new developers, need to clean this all up and do it right.
Suggestions from some developers on how it's currently generating the Id's is doing a "select max(id)+ 1 from TableXYZ". Now first off I don't beleive them becuase you can't explicitly/implicitly convert a varchar to an int. I also hate this model because it's wrong. I thought maybe they had a table that contorls the 'next id' of each other table and uses a stored proc wrapped in a TX to 'get the next id' but I can find no evidence of this AND some old, non working code, actually have "SELECT max(id) + 1 from TableXYZ. (Rolling my eyes at this.)
So I would like to hear how some of you would deal with this problem taking into account the following:
1- Doing it right is more important than doing it fast.
2- We don't understand that full 'scope' of all the data relations as the data structures are undocumented and related across multiple db's.
3- We can't bring these systems down for long periods of time and data is being inserted almost constantly.
Off the top of my head I came up with the following:
- ADD an IDENTITY field to TableXYZ (auto populated). Start the 'seed' at a number much greater than the current max value in the table. (To compensate for the growing data while we work on it).
- Add an insert trigger that will take the @@IDENTITY and store it in the current "ID".
Now I'm thinking 'Voila.. instant solution'. This allows us to use the @@IDENTITY variables to create all our relations, ensures old relations aren't broken (as we won't touch the old ID') and it has a minimal impact on the current architecture.
Any thoughts?