Solved

How do I seed sql server 2008 unique identity columns?

Posted on 2010-08-17
13
347 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:aspnet-scotland
13 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 33456594
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33456597
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
 
LVL 42

Expert Comment

by:dqmq
ID: 33456733
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33456798
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
 
LVL 42

Expert Comment

by:dqmq
ID: 33456814
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
 

Author Comment

by:aspnet-scotland
ID: 33462636
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:aspnet-scotland
ID: 33462670
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
 

Author Comment

by:aspnet-scotland
ID: 33462707
vdr1620,

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

Thanks.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33467537
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
 

Author Comment

by:aspnet-scotland
ID: 33468814
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
 
LVL 42

Expert Comment

by:dqmq
ID: 33469056
>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
 

Author Comment

by:aspnet-scotland
ID: 33469498
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
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 33470452
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now