Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Exclamation Unanswered: 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 Wim

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    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

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    How do you mean automatically ?

    Can you clarify ?

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    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

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Example please?

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    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
    Last edited by Wimmo; 04-15-04 at 11:14.

  8. #8
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    An example of what it would look like in a table.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    I like your solution, but the problem is that i cant set that column to primary key

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •