How to assign unique starting value to Identity Column's starting value [SQL Server 2014]

Question

For example, I have UserId column which is identity column of my table. When I add new data to table, UserId assign 1 automatically. If enter new data, the number in the column increases one by one.

UserId - UserName - UserLastName
1        asd        asd
2        xyz        xyz

I want to change UserId's starting value and increase it by my condition.

UserId - UserName - UserLastName
US1        asd        asd
US2        xyz        xyz

Is it possible to increase string values like above ?

I have 10 table, all of them have unique id and all of them like 1, 2, 3. I want to change one of them with some specific id — furkan altun, Sep 11 '17 at 09:51
You are making it complicated. But if you insist: it's easy to create a calculated column that puts a US in front of your existing identity values. But you'll eventually discover there is absolutely no reason to do what you're doing. — Nick.Mc, Sep 11 '17 at 10:03
Possible duplicate https://stackoverflow.com/q/2177584/1048425 — GarethD, Sep 11 '17 at 10:04

score 0 · Answer 1 · answered Sep 11 '17 at 10:00

0

If you are using Oracle database, you can create a trigger for that.

 Create or Replace TRIGGER "SYSTEM".Trigger_Name 
    BEFORE INSERT ON Table_Name 
    FOR EACH ROW 
    BEGIN
      <<COLUMN_SEQUENCES>>
      BEGIN
        IF INSERTING AND :NEW.UserID IS NULL THEN
          SELECT 'US'||to_char(Sequence_Name.NEXTVAL,'FM00') INTO :NEW.UserID FROM SYS.DUAL;
        END IF;
      END COLUMN_SEQUENCES;
    END;

answered Sep 11 '17 at 10:00

Jinesh Shah

922
10
18

I am using SQL Server 2014 – furkan altun Sep 11 '17 at 10:01
1

Please tag the question with SQL Server 2014 – Nick.Mc Sep 11 '17 at 10:03

How to assign unique starting value to Identity Column's starting value [SQL Server 2014]

1 Answers1