Solved

How do I seed sql server 2008 unique identity columns?

Posted on 2010-08-17
13
350 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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

17 Experts available now in Live!

Get 1:1 Help Now