Bigint autoincrement question

Hi All,
i wonder if i can get an bigint autoincrement field where the number begins with the current year + 1 autonumber
Does someone know if it is possible and if yes, how?
Already thanx.
Cheers Wimcreate table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go|||Originally posted by aldo_2003
create table test_t
(col1 bigint identity (2004, 1), col2 char (5)
)
go

declare @.counter tinyint

set @.counter = 1

while @.counter <> 5
begin
insert into test_t
select 'test'
set @.counter = @.counter + 1
end
go

select * from test_t
go

But it doen't change the year automatically|||How do you mean automatically ?

Can you clarify ?|||Originally posted by aldo_2003
How do you mean automatically ?

Can you clarify ?

The id should always start with the current year, so when it is 2005 the id should start with 2005 1 + increment|||Example please?|||Originally posted by aldo_2003
Example please?

It should be something like
create table test_t
(col1 bigint identity (datepart(yy, getdate())1, 1), col2 char (5)
)
go|||An example of what it would look like in a table.|||You should store your year value and your ID value separately, and then use a trigger to increment the ID value.

[YearColumn] defaults to year(getdate())

[IDColumn] is set by a trigger (Insert only!) to:

set [IDColumn] = isnull((select Max([IDCOLUMN]) from YourTable where [YearColumn] = Year(Getdate())), 0) + 1

Then you can combine the two values in your SQL code, or create a calculted column that combines the two.|||I like your solution, but the problem is that i cant set that column to primary key|||You can add a unique index to a computed column. You may be able to make it a primary key, thought I'm not certain about that. In any case, you could set the Year column and the ID column together as a composite primary key.