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
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);
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.
- 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:
- 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 /
_BIN2Collation is being used). You should have a “Gender” lookup table with a
GenderID TINYINTcolumn as the Primary Key (but not an
IDENTITY). Then in the
Customertable you would also have
GenderID TINYINTbut it would be a Foreign Key referencing
You could use a
CHAR(1) COLLATE Latin1_General_100_BIN2 NOT NULLcolumn with a
CHECKConstraint enforcing that only
Fare acceptable. This gets you a “code” that is human readable while still being as efficient with storage, memory, and comparisons as a
Read this excellent article please.
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)