Unique ID Without using Identity Number 3

[dazzer123]

I have a Database which contains records that need to have the unique ID

NE00000001
NE00000002
NE00000003

Etc.

I am unsure of how to create this unique ID field so that it is automatically entered on every insert. I dont really want to have to run some code for every insert as myself and a few others will need to be able to insert records straight into the DB bypassing the front end. Is there some sort of Identity Mask in SQL Server that I can use for this or any other way that you can think of that this could be done.

Any help or ideas would be appriciated.

 

[donutman]

Use an insert trigger, an identity column and your ID column. Upon insert use the identity column to create your char(10) field using the right and str functions:
'NE'+right(str(100000000+IdentityCol),8).
-Karl

 

[dazzer123]

Thats got it!! Excellant, thanks for that

 

[JohnDTampaBay]

You can accomplish the same thing with a computed column. Computed columns are easier to maintain and have less overhead.

CREATE TABLE SomeTable (
    SomeTableIntID   int  IDENTITY(1,1) NOT NULL
  , SomeTableCharID  AS 'NE'+Right(Str(100000000 + SomeTableIntID),8)
)

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[donutman]

Good point. I'll give you a star for that simple but insightful point! You method avoids the use of a trigger.
-Karl

 

[dazzer123]

Best make that a double star, donutman your solution was good but it has to be said Angels may be more efficiant

 

[dazzer123]

Just to help anyone doing anything similar here is what I actually done using angels and donutmans help

CREATE TABLE Table1(
Table1_SEED int IDENTITY(1,1) NOT NULL,
Table1_ID AS LEFT('NE000000', (8- LEN(LTRIM(STR(Table1.Table1_SEED))))) + LTRIM(STR(Table1.Table1_Seed))

)

 

[r937]

or

Table1_ID AS
'NE'+RIGHT('00000000'
+LTRIM(STR(Table1.Table1_Seed)),8)



rudy

http://r937.com/

SQL Consulting