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.

Read this excellent article please.

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Note that the dbID column is standard, database-generated identity which will be our physical primary key of the table. However, we will add a CustomerNumber column which will be what we expose to the outside world in the “C0000” format, as described.

Let’s create a function accepts an integer, and uses that integer to return our CustomerNumber:

create function CustomerNumber (@id int) 
returns char(5) 
as 
begin 
return 'C' + right('0000' + convert(varchar(10), @id), 4) 
end

Using that function, we can simply add a computed column to our table like this:

alter table Customers add CustomerNumber as dbo.CustomerNumber(dbID)