sql server - Using a table field as part of an identifier -

i have coded following table , use the text entered yearid add prefixed 'year' in cast().

for example if named year 2 yearid field populated 'year2'. apply next number in sequence after year e.g. year55 etc.

create table year(     groupid             int identity (10000, 1)  not null,     yearid              varchar (10) not null default 'year' +                              cast(next value non_identity_incrementer varchar(10)),     year                nvarchar (50) not null,     datetimemodified    datetime not null default sysdatetime(),     status              nvarchar(50) not null,     primary key clustered (groupid)  ); 

a computed column might solution:

create table [year] (     groupid             int identity (10000, 1)  not null,     yearid              'year' + [year],     [year]              nvarchar (50) not null,     datetimemodified    datetime not null default sysdatetime(),     status              nvarchar(50) not null,     primary key clustered (groupid)  ); 

when insert row, yearid equal 'year' + [year]:

insert [year]([year], status) values ('2', 'complete') select * [year] 

result:

groupid     yearid     year      datetimemodified        status ----------- ---------- --------- ----------------------- ------------ 10000       year2      2         2015-08-17 07:02:49.837 complete 

---

note computed column recalculated every time used in query, unless make persisted. making computed column persisted means physically stored in table. read here more information.