CODEWITHSUNDEEP
sql-server-2005
Zain Ali
Zain Ali

Reputation: 15953

Auto Generated Column in SQL Server

Is there any way to auto-generate a specific length column in SQL Server?

For example I want that a specific column default value will be a 12 digit unique number.

Upvotes: 0

Views: 4736

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

You can use a bigint identity with seed value 100000000000

create table T (id bigint identity(100000000000, 1), Col1 varchar(50))

And use a check constraint to prevent you from going to far.

alter table T with check add constraint CK_T_ID check (id<1000000000000)

Upvotes: 1

marc_s
marc_s

Reputation: 754418

You can have an BIGINT IDENTITY column that will generate unique BIGINT values - but they're not 12 digits by default....

Of course, based on a BIGINT IDENTITY, you could create a computed column something like this:

 ALTER TABLE dbo.YourTable
    ADD AutoField AS 'XY' + RIGHT('0000000000' + CAST(ID AS VARCHAR(10)), 10) PERSISTED

This way, you'd get values of 1, 2, 3, 4 in ID (as identity values), and XY0000000001, XY0000000002, and so forth in your computed AutoField column automatically.

That's pretty much the only "automatic" generation I know of (except for GUID's - but those are longer than 12 chars and not numeric).

What are you trying to do here'?

Upvotes: 1

Related Questions