Generating unique nvarchar unique key

Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks