0

Is there anyway to auto generate specific alpha numeric data in a primary key column? The column has maximum length of 2 characters.

The table looks like this:

id
----
4S
33
J6
US

I'm looking to create a stored procedure that inserts a random 2 alpha numeric characters value. Is this possible in SQL Server?

I need it to be auto generated with a combination like

id like '[A-Z][0-9]') 
  or (id like '[A-Z][A-Z]' )
  or (id like '[0-9][0-9]' )
  or (id like '[0-9][A-Z]' )
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ryan Gadsdon
  • 2,272
  • 4
  • 31
  • 56
  • 2
    Please provide sample data and desired results – SteveC Oct 25 '20 at 19:20
  • the table above is my current data – Ryan Gadsdon Oct 25 '20 at 19:21
  • 2
    If there are 36 possible characters and max length of 2 that only gives a max of 1,296 possible values before you get a PK violation – Martin Smith Oct 25 '20 at 19:22
  • i need it to be auto generated with a combination like id like '[A-Z][0-9]') or (id like '[A-Z][A-Z]' ) or (id like '[0-9][0-9]' ) or (id like '[0-9][A-Z]' ) – Ryan Gadsdon Oct 25 '20 at 19:22
  • 1
    @RyanGadsdon: what Martin Smith is pinpointing is that, with this technique, you can't have more than 1296 rows in your table. Are you sure that you are fine with this limitation? – GMB Oct 25 '20 at 19:35
  • 1
    This feels like an [XY Problem](http://xyproblem.info). You'd, at least, be better off using a `SEQUENCE` and converting the number to "key", `1` is `'01'`, `10` is `'0A'`, and `1296` is `'ZZ'`. Of course, as @MartinSmith says, this limits your table to 1,296 rows though; I suspect that isn't desired. – Thom A Oct 25 '20 at 19:35
  • its more a proof of concept if i can auto generate a column like this the same as you would with a identity column. I dont care about the row limit. Its to test the validity of the table constraint but i need it randomised in a stored procedure – Ryan Gadsdon Oct 25 '20 at 19:37
  • The problem with this "concept" is that with 1,296 unique values you have a *high* chance of generating a used value; that means you have to keep trying until you get a unique value, which could have significant impacts on performance. – Thom A Oct 25 '20 at 19:39
  • In short, as a proof of concept, this is a **bad** idea. – Thom A Oct 25 '20 at 19:41
  • On a separate note, due to the key being random, it won't be a good `CLUSTERED INDEX` candidate. That isn't specifically a "problem", however, it means you need/want a different column to be the `CLUSTERED INDEX` column; one that has an always ascending value. – Thom A Oct 25 '20 at 19:46

2 Answers2

0

Don't do it.

Primary keys are internal row identifiers, that are not supposed to be sexy or good looking. Their purpose is to uniquely identify rows and no more.

If you need a key to have some format it means you probably want to expose it somewhere, in the user interface or through an API; primary keys are not suitable to be exposed to the world.

Now, if you need to expose a key, then you can create a secondary key. This could be an extra column, maybe an auto-generated virtual column, or maybe it can be populated using a trigger.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

Actually you have 2 problems:

  1. Unique random number

You can find answers here: Generate list of new unique random numbers in T-SQL

  1. convert regular (base10) number to Base36 ('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')

For 2 characters length you can use this code:

DECLARE @Base10ToBase36 AS TABLE (
    ID      TINYINT NOT NULL IDENTITY(0,1) PRIMARY KEY,
    Symbol  CHAR(1) NOT NULL CHECK(Symbol LIKE '[0-9,A-Z]')
)

INSERT INTO @Base10ToBase36(Symbol)
VALUES  ('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), 
        ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'),
        ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
        ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')

SELECT  TOP (1) 
        pos1.ID * 36 + pos0.ID AS Base10Number,
        pos1.Symbol,
        Pos0.Symbol,
        CONCAT(pos1.Symbol, Pos0.Symbol) AS Base36
FROM    @Base10ToBase36 AS pos0
        CROSS JOIN @Base10ToBase36 AS pos1
ORDER BY NEWID()    -- random

About random you can get more explanation here: Random record from a database table (T-SQL)

Regarding primary key - I'm totally agree with @TheImpaler. It's bad idea

AlexK
  • 9,447
  • 2
  • 23
  • 32