How do I seed sql server 2008 unique identity columns?

Hi,

I have the attached sql server 2008 db design. The basic concept of the design is to store contract agreement information for companies wishing to purchase software tools from us, i.e. A parent company (Microsoft) will have child companies (Microsoft UK, Microsoft US etc). Each child company will have key contacts for correspondance reasons (Financial Contact, Technical Support Contact etc). Each child company will also have contract agreements with us in regard to purchasing our software tools, now I would like to structure my uniqueid values (AGRID and AGRADID) at this stage to accomodate for relevant business logic. I would like AGRID to remain as a unique int value but of the format "0000001 - 00" if possible? i.e. First row: 0000001 -00, Second row: 0000002 - 00 etc. The next unique value I would like to format is AGRADID, here I would like it's value to be of the format "<AGRID> - 01" i.e. First row: <AGRID> - 01, Second row: <AGRID> - 02 etc while obviously upholding the relationship rules between these tables. Is this achievable through some sort of seeding rule or computed value? Or can my db design be somehow altered to achieve this?

Thanks.
agr.jpg
aspnet-scotlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
You can use computed columns which reformat the integer values on the fly for presentation purposes. Phone's ringing, we'll get back to you.
0
HainKurtSr. System AnalystCommented:
you can use int and no change underlying tables...
just format when you query it...

101 -> 000001-01
102 -> 000001-02
201 -> 000002-01
..
33401 -> 000334-01

etc...
0
dqmqCommented:
For the first table, it's easy. do like this:

Alter table AgreementContract
add agrid_formatted as right('000000000' + cast(agrid as varchar(10)),10) + '-00'


0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

vdr1620Commented:
Probably,You can do this by altering your Design a little by adding an identity Column to your table and then Set your AGID based on Identity Column like below

See the below example
create table #E
(
int_id int identity(1,1),
AGID AS CONVERT(VARCHAR(100),('000000')+ CONVERT(Varchar(20),int_id)+ '-00'),
A Varchar(20)
)

INSERT INTO #E (A) VALUES (1)
INSERT INTO #E (A) VALUES (2)
INSERT INTO #E (A) VALUES (3)


SELECT * FROM #E

Open in new window

0
dqmqCommented:
The second table is more challenging because of the way your key is structured, which leads me to ask a question.  

There is no problem with the prefix part of the expression because it is derived from real data, i.e. agrid.  But the suffix part is based on "first" line and "second" line, which is not so easy to derive.  

Suppose you have four rows in that table that look like this (with the formatted column included)

1,1  -->  '00000001-01'
2,2  -->  '00000002-01'
3,2  -->  '00000002-02'
4,2  -->  '00000002-03'


Then suppose the agradid=3  row gets deleted:

1,1  -->  '00000001-01'
2,2  -->  '00000002-01'
4,2  -->  '00000002-03'  or  '00000002-02'  

How do you then want agradid=4 to be formatted?




0
aspnet-scotlandAuthor Commented:
dgmg,

If the agradid=3  row gets deleted I would like agradid=4 to be formatted in it's place, i.e.  '00000002-03'

However, I'm not too sure if I'm even going to allow deletes from this table as I want to preserve all rows for historical reasons, would this make your solution simpler?

Thanks.
0
aspnet-scotlandAuthor Commented:
HainKurt,

These formatted numbers are for presentation purposes but obviously they still need to be under database table relationship rules for maintenance reasons.

Another note I must add is that I will be using LINQ queries to pupulate my frontend controls. Could you provide me with a sample linq query that will format my int as desired?

Thanks.
0
aspnet-scotlandAuthor Commented:
vdr1620,

I understand how your suggestion will work for the first table but how about for the second table "AgreementAddendum"?

Thanks.
0
dqmqCommented:
The difficulty with reformatting the AgreementAddendum identifier is that the line number is RELATIVE rather than ABSOLUTE.  In otherwords, it is subject to change over time if intervening rows are deleted/added or (heaven help us) the AgrADID changes.  Preventing deletes would certainly lessen the risk, but there are so many ways and reasons that data gets changed, you will always be vulnerable to a back-door screw up.  I point this out because you seem concerned about the integrity of that number--but I am not in a position to assess the risk.

For the same reason, you cannot even reliably store the formatted value as a column in the table.

You can format the relative identifier dynamically and you can even manage that derivation in a view, but the performance will not be so good:

Create View AgreementAddendumView as
Select *, (Select count(*) from AgreementAddendum A2 where A2.AgrID = A1.AgrID and A2.AgrADID <= A1.AgrID) AgrADID_Formatted
from AggreementAddendum A1
go
Select * from AgreementAddendumView

Perhaps a better compromise is to redesign the AgreementAddendum key to a composite key.  The first column being AgrID and the second column being a line number within AgrID.   Then you could add a computed column similar to the technique used for the first table.
 

 

The simplest comprise is to assign AgradID within AgrID and permanently keep that  

0
aspnet-scotlandAuthor Commented:
dgmg,

Perhaps a better compromise is to redesign the AgreementAddendum key to a composite key.  The first column being AgrID and the second column being a line number within AgrID.   Then you could add a computed column similar to the technique used for the first table - Could you clearly state the columns required for this approach and the relationship needed between both my AgreementContract and AgreementAddendum tables.

I was also reading into triggers, would this be a viable approach at all for going forward? - I know this approach wouldn't implement my new business logic within existing data but at least it would for future data.

Thanks.
0
dqmqCommented:
>Could you clearly state the columns required for this approach and the relationship needed between both my AgreementContract and AgreementAddendum tables.


AgreementContract
AgrID (PK)

AgreementAddendum
AgrID (PK),(FK references AgreementContract.AgrID)
AgrAdNo (PK)   --sequential number within Contract, i.e. the addendum suffix

Triggers:
Not sure what you have in mind, but I don't see it as a viable solution for the problem at hand.  The addendum suffix ispart of what identifies an addendum.  If it's a fixed number (01,02,03, etc) that never changes for a given addendum, then life is good.  If it's a relative number that's derived by counting other addendums, then life is difficult.  

FWIW, I don't know how the '0000002-01' references are used in your business, but it seems to me that things would get pretty confusing if the suffix part changed over time!



0
aspnet-scotlandAuthor Commented:
dgmg,

The suffix 01, 02, 03 etc will not be changing.

So I'm incrementing AgreementContract.AgrID (0000001, 0000002, 0000003 etc) by your original computed column but what about AgrAdNo (01, 02, 03 etc)? This int by default will increment 1, 2, 3 not 01, 02, 03 etc won't it? Or is this again the result of another computed column?

Thanks.
0
dqmqCommented:
Yes, the int will increment 1,2,3.  I showed it as 01, 02, 03 because that's how you wanted it reformatted.

Your computed column would then look like this:

alter table AgreementContract
add agrid_formatted as right('000000000' + cast(agrid as varchar(10)),10) + '-' +
  right('00' + cast(AgrAdNo as varchar(2)),2)


You would also do well to add a check constraint to keep AgrAdno between 1 and 99
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.