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

Generating unique id strings

Hi
I want to generate a unique record number in the following format
Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)

Could someone recommend how I go about generating this number?

Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?

... or should I just create a stored procedure to generate the number each time?

... or should I do something completely different?

Regards
John :confused:First, I'd recommend against doing this unless this code is required by the business process, i.e. recognized by the system users. Such codes are of little or no value to the application.

If all four of these values already exist in the table, then you could add a calculated column to your table that concatenates them to form the ID. These types of keys are often called "Superkeys", but with the availability of composite keys they are of little use these days and are often difficult to maintain.|||Thanks blindman, I'll take your advise and k.i.s.|||you could create a formula to concatenate the other fields and use it as the default value of a new field in the same row

I don't think I explained this very well

er

read about using formulas as default values in BOL

GW|||As a default value it would not update automatically if any of it's components change. That's the advantage of a calculated field.|||Apologies

U right of course blindman

I was'nt thinking

GW

Generating Unique Check Digits

Hi all,
I've implemented a small routine that generates "ID" numbers in hexadecimal
format. Does anyone know of any routines to generate a unique check digit
for a hexadecimal number? I've found several ways to do it in Base 10, but
not so much for hex. I really like the UCC mod 10(3) methods, but the check
digits are fairly linear in progression when I try to apply it to hex.
Here's an example of the types of ID's I'll be generating:
B951-D300-1E73-C1xx
Note that the dashes are just to "prettify it", and can be ignored for my
purposes. "xx" represents a placeholder for the 2 hexadecimal check digits.
B951D3001E73C1xx
Thanks. Any help is appreciated.Michael,
Do you need the check digit for more than
catching transcription or typing errors? Is there
a reason "fairly linear" in progression is not acceptable?
Steve Kass
Drew University
Michael C# wrote:

>Hi all,
>I've implemented a small routine that generates "ID" numbers in hexadecimal
>format. Does anyone know of any routines to generate a unique check digit
>for a hexadecimal number? I've found several ways to do it in Base 10, but
>not so much for hex. I really like the UCC mod 10(3) methods, but the chec
k
>digits are fairly linear in progression when I try to apply it to hex.
>Here's an example of the types of ID's I'll be generating:
>B951-D300-1E73-C1xx
>Note that the dashes are just to "prettify it", and can be ignored for my
>purposes. "xx" represents a placeholder for the 2 hexadecimal check digits
.
>B951D3001E73C1xx
>Thanks. Any help is appreciated.
>
>|||Like you mentioned, catching transcription errors is part of it; I also
don't want people to be able to reverse engineer and guess the previous ID
code; for instance, if the code ends with -C1B6, I don't want them to be
able to take a wild guess at -C0B5 being the previous ID.
Thanks
"Steve Kass" <skass@.drew.edu> wrote in message
news:OYxYyK7SFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Michael,
> Do you need the check digit for more than
> catching transcription or typing errors? Is there
> a reason "fairly linear" in progression is not acceptable?
> Steve Kass
> Drew University
>
> Michael C# wrote:
>sql

Generating numbers in SQL

hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem?. Are there any samples/codes available for this.

Any help would be highly appreciated.

thanks,
-sriramWhat are you doing this for? Uniqueness for records in a table? You can use an Identity Column for that...what is the business problem you are trying to solve?|||thanks for your reply.

My client wants to do it in the way i explained before. Is there a way??

-sriram|||You could do it a few ways, the incrementing number being the point of divergence. To get the current month you simply use the Month() function with GetDate()

ex:
SELECT Month(GetDate())

There are a number of ways to build the second part...

One would be to create an identity column and after the insert read the SCOPE_IDENTITY(), combine it with the Month(GetDate()) to get your number. The problem with this is that it relies on work after the insert which could be done through a stored proc or a trigger.

Another approach would be to select the top value from the column, trimming the month prefix and the creating your value by incrementing that number.

Do you need to stuff with zeros? Does the month need to be 1 or 01; does the second number need to be led by zeros?|||hi,

thanks for replying.

The second number needs to be led with zeros.

thanks,

-sriram|||You can use the SQL-Server Function Replicate() for this:

DECLARE @.MaxLength AS int
SET @.MaxLength = 6
DECLARE @.INT AS int
SET @.INT = 1
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 000001
SET @.INT = 1234
PRINT REPLICATE('0', @.MaxLength - LEN(LTRIM(STR(@.INT))) ) + LTRIM(STR(@.INT))
-- >> prints 001234

|||Here is some code that will help; I tried not to use UDFs because I dont know what version of SQL you are using. Regardless this will show you how to parse the old value out and generate a new one.

-- Assumes that the fixed length is 10

Declare @.OldValuechar(10)
Set@.OldValue= '0100000365'

Declare @.OldNumberint
Set@.OldNumber= Cast(Substring(@.OldValue,3, LEN(@.OldValue)-2) as int)

Declare @.MonthNamevarchar(10)
Set@.MonthName= DateName(M, Cast(Left(@.OldValue,2) + '/01/2000' as datetime))

-- Show the old values
select @.OldValue
select@.OldNumber
select @.MonthName

Declare @.MonthPrefixchar(2)
Set@.MonthPrefix= (Select Case WHEN Month(GetDate()) >= 10 THEN Cast(Month(GetDate()) as char(2)) ELSE '0' + Cast(Month(GetDate()) as char(1)) END)

Declare @.NewNumbervarchar(8)
Set@.NewNumber= Cast(@.OldNumber + 1 as varchar)

Declare @.Prefix varchar(7)
Set@.Prefix= ''

While Len(@.Prefix) < (7 - len(@.NewNumber))
Set @.Prefix = '0' + @.Prefix

Declare @.NewValuechar(10)
Set@.NewValue= @.MonthPrefix + @.Prefix + Cast(@.NewNumber as char)

-- Show the New Value
Select @.NewNumber
Select@.NewValue|||thanks,

i think it makes sense.

it should work..

anyway thanks for your reply.

-sriramsql

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

Generate Unique Alpha numeric Value

Hi all
Can any one send me the procedure which can give me
unique alpha numeric value , that should not repeat..
thanksUse the NEWID() function. This will generate a GUID which can be implicitly converted to and from a CHAR(36) or NCHAR(36) data type.

blindman

generate Identity field on existing table

Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?

Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.

Thanks in advance!do you mean programmatically ? you can always add it in the design view...

hth|||If you add a new column and set its type to INT and set Identity to true, it will auto populate based on the Identity Seed and the Identity Increment.|||Thanks for the replies. I figured it out right after my first posting. DOH! :)

Generate a unique alphanumeric password

Hi There!
Does anybody know how to generate unique alphanumeric passwords in SQLServer?
You help will be very much appreciated :)
:) :)
Mel!Hello,

I don't think it is possible in SQLServer.
You can try:
http://www.burney.ws/software/demos/password_generator/

Kind regards,

Jan|||Originally posted by melaniemayfield
Does anybody know how to generate unique alphanumeric passwords in SQLServer?


I guess you don't think GUID is a nice password? ;-)

Just using RAND you can create some random passwords like this...

DECLARE @.PwdLen SMALLINT,
@.Chr TINYINT,
@.Password VARCHAR(20)

-- Seed
SET @.Chr = RAND(DATEPART(ms, GETDATE())) * 0
SET @.Password = ''

SET @.PwdLen = 1
WHILE @.PwdLen < 8
BEGIN
SET @.Chr = RAND() * 62
SET @.Password = @.Password + CHAR(
CASE WHEN @.Chr < 10 THEN @.Chr + 48
WHEN @.Chr BETWEEN 10 AND 35 THEN @.Chr + 55
ELSE @.Chr + 61
END)

SET @.PwdLen = @.PwdLen + 1
END

SELECT @.Password [Password]

Perhaps can get you something to work from.

Cheers,
Robert