Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sumo
Starting Member
45 Posts |
 Posted - 2002-11-14 : 13:38:15
|
Is it possible to have a field that is a character datatype used as an auto-incrementable identity? The identity data will look like numbers, but they need to be a fixed length (with leading zeros, i.e. 001). We have a very valid reason for wanting to do this and I don't know an easy way to do it without an insert trigger to generate the next #.Would it be easier to use int or smallint, start the seed at the first # that matches the length I want and use a rule to limit how high it can go (i.e. seed = 100, rule <= 999)? We're going to be using these identities in many different tables to build a large cross-reference table that has all of the id's concatenated together into a character field (i.e. 037858834064).The explanation as to why we need to do this is rather lengthy to explain here...  MichaelMichael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-14 : 14:45:03
|
| I would say that storing the data in a table and how you use it elsewhere don't always have to be exactly the same.For example, use an int with a constraint of <999, if 3 digits is your limit, for your primary key. Then, create a VIEW of that table that adds a field which is a char(3) that is your primary key with leading zeroes if needed. Then, you can use that field going forward in other tables.One other thought: instead of concatenating ID's into one big field, I would suggest a "middleman" table joining ID's from 1 table with ID's from another.That is, instead ofOtherKey ForeignIDField1 0010020030040052 001006008009You would have:OtherKey ForeignID1 0011 0021 0031 0041 0052 0012 0062 0082 009And the primary key of the above table would be (OtherKey, ForeignID).Good Luck.BTW -- to pad your ID field into 3 digits:Right('000' + Convert(varchar,ID),3) |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-15 : 04:41:45
|
| >> We have a very valid reason for wanting to do this.Doubt that.But have you looked at computed columns?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2002-11-15 : 08:09:54
|
quote:
>> We have a very valid reason for wanting to do this.Doubt that.But have you looked at computed columns?
Actually, there's no need for either. I looked at it a bit more and I can use an integer identity with the seed being the smallest # possible at the length I want. For example, if I want a 3 digit #, I start at 100 and end at 999, 4 digits, 1000 to 9999. It does eliminate usable #'s, but what we're building is a lookup table with a 57 character long string and an ID. The string will contain all of the fixed-length codes in character form from previous selections (it's an automotive catalog) and a generic search can be done on the string using like. It allows us to make our entire catalog available as a web service without defining non-generic calls to the service to generate the search. The less querying the better and we'll be making it fast by creating federated databases and partitioned views... It's really difficult to explain the complexities of an automotive parts catalog (without a few days to spare). Especially since we're talking about 1.5 million parts in over 650 manufacturers. The catalog we're rebuilding is a vehicle application catalog and we need to give universal access from anywhere...Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
anas
Starting Member
1 Post |
Posted - 2005-12-13 : 01:44:35
|
| to make your identity field starts from 0001 ,make the field int , seed = 1 and format=0000, so the next will be 0002 etc. |
|
|
|
|
|
|
|
|
Using char as an auto-increment identity field