Sql Server Column with Auto-Generated Data

Question

I have a customer table, and my requirement is to add a new varchar column that automatically obtains a random unique value each time a new customer is created.

I thought of writing an SP that randomizes a string, then check and re-generate if the string already exists. But to integrate the SP into the customer record creation process would require transactional SQL stuff at code level, which I'd like to avoid.

Help please?

edit: I should've emphasized, the varchar has to be 5 characters long with numeric values between 1000 and 99999, and if the number is less than 10000, pad 0 on the left.

Answer 1

if it has to be varchar, you can cast a uniqueidentifier to varchar.

to get a random uniqueidentifier do NewId()

here's how you cast it:

CAST(NewId() as varchar(36))

EDIT

as per your comment to @Brannon:

are you saying you'll NEVER have over 99k records in the table? if so, just make your PK an identity column, seed it with 1000, and take care of "0" left padding in your business logic.

Answer 2

Does the random string data need to be a certain format? If not, why not use a uniqueidentifier?

insert into Customer ([Name], [UniqueValue]) values (@Name, NEWID())

Or use NEWID() as the default value of the column.

EDIT: I agree with @rm, use a numeric value in your database, and handle the conversion to string (with padding, etc) in code.

Answer 3

Try this:

ALTER TABLE Customer ADD AVarcharColumn varchar(50) 
CONSTRAINT DF_Customer_AVarcharColumn DEFAULT CONVERT(varchar(50), GETDATE(), 109)

It returns a date and time up to milliseconds, wich would be enough in most cases.

Do you really need an unique value?

Answer 4

This question gives me the same feeling I get when users won't tell me what they want done, or why, they only want to tell me how to do it.

"Random" and "Unique" are conflicting requirements unless you create a serial list and then choose randomly from it, deleting the chosen value.

But what's the problem this is intended to solve?

Answer 5

With your edit/update, sounds like what you need is an auto-increment and some padding.

Below is an approach that uses a bogus table, then adds an IDENTITY column (assuming that you don't have one) which starts at 1000, and then which uses a Computed Column to give you some padding to make everything work out as you requested.

CREATE TABLE Customers (
    CustomerName varchar(20) NOT NULL
)
GO

INSERT INTO Customers 
SELECT 'Bob Thomas' UNION
SELECT 'Dave Winchel' UNION
SELECT 'Nancy Davolio' UNION
SELECT 'Saded Khan'
GO

ALTER TABLE Customers
    ADD CustomerId int IDENTITY(1000,1) NOT NULL
GO 

ALTER TABLE Customers
    ADD SuperId AS right(replicate('0',5)+ CAST(CustomerId as varchar(5)),5) 
GO

SELECT * FROM Customers
GO

DROP TABLE Customers
GO

Answer 6

I think Michael's answer with the auto-increment should work well - your customer will get "01000" and then "01001" and then "01002" and so forth.

If you want to or have to make it more random, in this case, I'd suggest you create a table that contains all possible values, from "01000" through "99999". When you insert a new customer, use a technique (e.g. randomization) to pick one of the existing rows from that table (your pool of still available customer ID's), and use it, and remove it from the table.

Anything else will become really bad over time. Imagine you've used up 90% or 95% of your available customer ID's - trying to randomly find one of the few remaining possibility could lead to an almost endless retry of "is this one taken? Yes -> try a next one".

Marc