Auto Increment in MS SQL letter and number

I have one question, how to achieve auto increment in MS SQL. the one way i know so far is to perfix in the table eg

CREATE TABLE Customer (
    CUSId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,CUSKey AS 'Cus' + RIGHT('000' + CONVERT(VARCHAR(5), CUSId), 6) PERSISTED
    ,CusName VARCHAR(50)
    ,mobileno INT
    ,Gender VARCHAR(10)
    )

i will get something like

Cus0001
Cus0002

what after Cus9999 ?

some how can i achive auto increment like this

CUSAB000001
CUSAB000002
CUSAB000003
CUSAB000004
CUSAB000005
CUSAB000006
CUSAB000007
CUSAB000008
CUSAB999999
CUSCD000001
CUSYZ999999.

In order to increment groups starting at AA and ending at ZZ, you need to convert the left portion of the number (whatever is above the number of digits you want to keep as integers) into Base 26. You start by truncating the right portion of the number so that you have only the left portion, then you divide by 10 ^ IntegerDigits (e.g. 3 integer digits == 10 ^ 3 == 1000) to get the A_ side, then use modulo on the same 10 ^ IntegerDigits to get the _A side. Those values will get us the offset from the ASCII value for A. For example:

SELECT (123142 / 1000) AS [Truncated],
       (123142 / 1000) / 26 AS [SetsOf26],
       (123142 / 1000) % 26 AS [Remaining],
       CHAR(65) AS [A];

-- Truncated = 123
-- SetsOf26  = 4
-- Remaining = 19
-- A         = A

We can put all of that together in an Inline TVF (for demo purposes, not for using in a computed column in a table) as follows:

CREATE FUNCTION dbo.Base26 (@Base10 INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH truncated AS
  (
    SELECT (@Base10 / 1000) AS [Value]
  )
  SELECT @Base10 AS [Actual],
    tr.Value AS [Truncated],
    CHAR(65 + (tr.Value / 26)) AS [1stChar],
    CHAR(65 + (tr.Value % 26)) AS [2ndChar],
    CHAR(65 + (tr.Value / 26))
      + CHAR(65 + (tr.Value % 26))
      + RIGHT('00' + CONVERT(VARCHAR(20), @Base10 % 1000), 3) AS [EndResult]
  FROM truncated tr;
GO

Then we can test with:

SELECT * FROM dbo.Base26(142);
SELECT * FROM dbo.Base26(3142);
SELECT * FROM dbo.Base26(123142);
SELECT * FROM dbo.Base26(123999);

which returns:

Actual Truncated 1stChar 2ndChar EndResult
142 0 A A AA142

3142 3 A D AD142

123142 123 E T ET142

123999 123 E T ET999


HOWEVER, I see absolutely no reason at all to implement this in your situation. There is no benefit in having a string "CusXXXXXX" value where "XXXXXX" is really just the IDENTITY value. If you were going to obfuscate the "XXXXXX" via a hash or Modular Multiplicative Inverse, then it might be ok, though I am still not sure you would want to store the "Cus" string portion of it. But in its current form, you get no benefit whatsoever from doing this. You are just wasting space in the DB.

Other notes:

• You would be better off not abbreviating "Customer" to be "CUS" as a prefix for columns in this table. Just use the full table name, especially for the ID column: CustomerID, and CustomerName.

• You should not use a numeric type (i.e. INT) to store phone numbers. Numbers that you do not do mathematical operations on should be stored as strings. This also applies to postal codes, Social Security Numbers (SSNs), etc. In the case of phone numbers, they often have extensions or other non-numeric options, such as prefixing with + if they are outside of the base country.

-
You should not use a string type (i.e. VARCHAR(10)) to store a repeated code / label such as "gender" (assuming that the full words "male" and "female" or similar are being stored, and that a non-binary / _BIN2 Collation is being used). You should have a "Gender" lookup table with a GenderID TINYINT column as the Primary Key (but not an IDENTITY). Then in the Customer table you would also have GenderID TINYINT but it would be a Foreign Key referencing dbo.Gender (GenderID).

OR:

You could use a CHAR(1) COLLATE Latin1_General_100_BIN2 NOT NULL column with a CHECK Constraint enforcing that only M and F are acceptable. This gets you a "code" that is human readable while still being as efficient with storage, memory, and comparisons as a TINYINT would be.

StackExchange Database Administrators Q#146117, answer score: 8