AlphaNumeric code generator
-
Hello,
I am wondering if anyone has an algorithm or code that they are willing to share to generate a CHAR(10) alphanumeric code.
Thanks,
R
-
Can you explain a bit more what are you trying to accomplish? Char(10) is the equivalent of line-feed and if you know it why would you "generate it" ????
* Noel -
I think he means 10 characters long....not char(10) - right ?!
If so, do you have any business rules that dictate how they should be generated ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Ah !!! that may be the case ( I am not good at guessing
)Well without more info on how the generation is supposed to be we will be guessing again
* Noel -
Why not just take 10 random characters out of a call invoking NEWID().......
-
Hello All,
Sorry for not mentioning the case clearly.
Sushila guessed it correctly. It's basically generating a alphanumeric key.
For example, in the following table
dbo.Table1 (
Col1 VARCHAR(10) PRIMARY KEY NOT NULL
, Col2 INT
, Col3 SMALLDATETIME)
I would like to insert into Col1 an unique alphanumeric value that is generated by a stored procedure or some other piece of software.
Please suggest any alternative approaches.
Thank you,
R
-
ram4tech....usually with alphanumerics there's some company/business rule that dictates that the alpha part must be (eg: first 3 letters of dept.; first name initial+ first 3 chars of last name...region code..etc.) and the numeric part also reflects something that identifies uniquely the values in that row (could be empID, deptID, date joined... etc..)...
why must your unique value be alphanumeric and why must it be 10 characters long - eg: why can't you set your col1 as an identity type ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila,
There is no business rule that would dictate any set of guidelines to create this key. Thought about using identity, still debating on using identity. So if you guys have used a generic procedure, please let me know.
Thanks,
R
-
Andrew's suggestion of newid maybe the solution then...here's something from BOL on Unique indentifiers and the pros & cons...
Using uniqueidentifier Data
The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.
A GUID value for a uniqueidentifier column is usually obtained:
In a Transact-SQL statement, batch, or script by calling the NEWID function.
In application code by calling an application API function or method that returns a GUID. The Transact-SQL NEWID function and the application API functions and methods generate new uniqueidentifier values from the identification number of their network card plus a unique number from the CPU clock. Each network card has a unique identification number. The uniqueidentifier returned by NEWID is generated using the network card on the server. The uniqueidentifier returned by application API functions and methods is generated using the network card on the client.
A uniqueidentifier is not typically defined as a constant because it is difficult to ensure that the uniqueidentifier created is actually unique. There are two ways to specify a uniqueidentifier constant:
Character string format
'6F9619FF-8B86-D011-B42D-00C04FC964FF'
Binary format
0xff19966f868b11d0b42d00c04fc964ff
The uniqueidentifier data type does not automatically generate new IDs for inserted rows the way the IDENTITY property does. To get new uniqueidentifier values, a table must have a DEFAULT clause specifying the NEWID function, or INSERT statements must use the NEWID function:
CREATE TABLE MyUniqueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10) )
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('abc')
INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')
GO
uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column. A foreign key column referencing a uniqueidentifier primary key in another table will have multiple occurrences of individual uniqueidentifier values when multiple rows reference the same primary key in the source table.
A table can have multiple uniqueidentifier columns. One uniqueidentifier column for each table may be specified with the ROWGUIDCOL property. The ROWGUIDCOL property indicates that the uniqueidentifier values in the column uniquely identify rows in the table. The property does not do anything to enforce this, however. The uniqueness must be enforced through other mechanisms, such as specifying the PRIMARY KEY constraint for the column. The ROWGUIDCOL property is primarily used by SQL Server replication.
The main advantage of the uniqueidentifier data type is that the values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world.
The uniqueidentifier data type has several disadvantages:
The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
The values are random and cannot accept any patterns that may make them more meaningful to users.
There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.
Consider using the IDENTITY property when global uniqueness is not necessary, or when having a serially incrementing key is desirable.
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply