Create default unique id such as U82300001 etc 1

[willdevelope]

How do you create unique identifier such as U82300001 ,U82300002 and U82300003 etc to autogenerate for each row ?

 

[gmmastros]

Well.... you kinda can't. At least not easily.

What you can do is have an identity column that is an integer. You can make this identity column start at 82300001 and increment by one. Then, you could create a computed column that preprends the 'U'.

For Example:

Create Table InterestingIdentity(RowId Int Identity(82300001,1), Data VarChar(20), Id As 'U' + Convert(VarChar(8), RowId))

Now, when you use this table:

Insert Into InterestingIdentity(Data) Values('Blue')
Insert Into InterestingIdentity(Data) Values('Red')

Select * From InterestingIdentity

To clean up:

Drop Table InterestingIdentity

As you can see, there is a real integer identity column that handles the number part, and a computed column that handles the 'U' + Number part.

If your table already exists...

Create a table without identity

Create Table InterestingIdentity(Data VarChar(20))

Modify the table to add an identity and a computed column

Alter Table InterestingIdentity Add RowID Int Identity(82300001, 1), Id As 'U' + Convert(VarChar(8), RowId)

Add some data

Insert Into InterestingIdentity(Data) Values('Blue')
Insert Into InterestingIdentity(Data) Values('Red')

Select * From InterestingIdentity

Clean up...

Drop Table InterestingIdentity

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Why?
What's wrong with IDENTITY column if the prefix is always 'U'?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.