Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Sorry if that's not overly clear.

In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end?

So for example, say I have column A.

  • If I set row 1 in column A to "Foo", the final value will be "Foo1"
  • Then if I set row 2 in column A to "Bar", the final value is "Bar2"

Is this possible?

share|improve this question
up vote 3 down vote accepted

Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED

Assuming ID is where you're getting row 1 or row 2 value.

share|improve this answer
Thanks very much! – JMK Sep 16 '13 at 9:19

Another a way would be based on a view. If you can't modify the source table - often the case for 3rd party products - a view can save your day. Like so,

create table autoappend(id int identity(1,1) , data nvarchar(32))
insert into autoappend(data) values ('foo')
insert into autoappend(data) values ('bar')
insert into autoappend(data) values ('zof')

create view vAppend as
    select id
        , data
        , appended = data + convert(nvarchar(8), id)  -- concat() is SS2012+ only
    from autoappend

select * from vAppend


id  data    appended
1   foo foo1
2   bar bar2
3   zof zof3
share|improve this answer
appended is a column alias. I suggest you remove the single quotes. – ypercubeᵀᴹ Sep 16 '13 at 11:10
@ypercube, Thanks. Didn't remember that. I Guess old bad habits are hard to kick – vonPryz Sep 16 '13 at 11:17

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.