Generating Random ID's in Microsoft SQL Server

Question

I want to create a table in sql server and fill it up with data (people's info) every person should have a unique ID different than the auto incremented ID's by sql server For example i need the ID for the first person inserted like this: 2016xxxx how to fix the 2016 and randomly generate the numbers after that to be filled instead of xxxx should i use a regular expression ?

Possible duplicate of [How do I generate random number for each row in a TSQL Select?](http://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) — RB., Jan 31 '16 at 22:35
and when the year is 2017 I guess you want this number to be 2017? — Nick.McDermaid, Jan 31 '16 at 22:55
@Nick.McDermaid exactly i want to increment it by the year we are in, but now my question is how to do so ? i have a deadline to make it and it must work — Nader Al-Sabbagh, Jan 31 '16 at 23:12
When you followed the link above, exactly how did it "not work"? If you want to reach your deadline you need to make some kind of effort. — Nick.McDermaid, Jan 31 '16 at 23:15
The answer on how to generate a random number is in there. You need a random number between 0 and 9999... The linked answer shows you how: `ABS(CHECKSUM(NewId())) % 10000`. Add `20160000` to it and you're done. — TT., Jan 31 '16 at 23:26

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

You could create a function that would get the next value for you and use that instead of an AUTO_INCREMENT field.

I wouldn't recommend it tho. You shouldn't format the data like that before inserting it. That sort of thing should be done on the way out, preferably by the front-end code. Or you can just write a query and create a view ... However if you must do that here is the complete answer with the code: Is there a way to insert an auto-incremental primary id with a prefix in mysql database?

edited May 23 '17 at 10:27

Community

1
1

answered Feb 01 '16 at 01:21

Iman

647
13
30

That's for MySQL but yes, your advice is sound - it's not a good idea to do that. – Nick.McDermaid Feb 01 '16 at 02:06

score 0 · Accepted Answer · answered Feb 01 '16 at 07:08

You can also create a computed column like below

CREATE TABLE tableName
(
    PkAutoId INT PRIMARY KEY IDENTITY(1,1),
    PersonUniqueNo AS (CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR) + RIGHT(RIGHT(CAST(RAND() AS VARCHAR),4) + CAST(PkAutoId AS VARCHAR),4))
)

Computed Column "PersonUniqueNo" is 8 Digit Unique Number comprising of Current Year And Conceited value of Random number and Primary Key Id for 4 Length, Total length will be 8 as asked.

Generating Random ID's in Microsoft SQL Server

2 Answers2