Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have table something like this:

CREATE TABLE [dbo].[ado_test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tip] [varchar](10) NOT NULL,
    [datum] [datetime] NOT NULL,
    [doc_number] [int] NULL
) ON [PRIMARY]

I want column doc_number have sequential numbers for each tip. To be clear, I want to automatically or default or use a computed column with a function like this:

create FUNCTION [dbo].[fn_test_number]
(
     @tip varchar(10)
    ,@datum datetime
)
RETURNS int
AS
BEGIN
    DECLARE @number int

    select
        @number=    max(doc_number)+1
    from ado_test_table
    where
        year(datum) = year(@datum)
        and tip = @tip

    set @number=isnull(@number,1);

    return @number
END

In SQL Server 2005, can I have a column which will automatically have values? Should this be done by computed column, default (constraint) or trigger?

share|improve this question
    
Why do you need this? What should happen if the row with the max serial number for a tip is deleted? –  Martin Smith May 31 '13 at 9:08
    
@MartinSmith That number is reference for humans, Every doc have number own number. For me it is common case for apps, How do you count bills. Considure this table as bills holder I need to show users some bill number. But I have different type of bills and each have own numbers –  adopilot May 31 '13 at 9:17
    
Do you mean it would only be used for display purposes? –  Andriy M May 31 '13 at 11:23
    
@AndriyM yes that is purpose of calculated field –  adopilot May 31 '13 at 12:13
    
You don't need a column for that, unless we are misunderstanding each other. You could use ROW_NUMBER to generate row numbers every time the query is run. If you need fixed numbers, however, then that is what the IDENTITY property is for, and you are already using it for one of the columns. Of course, that cannot enumerate different types of bills independently, if that's what you are after. –  Andriy M May 31 '13 at 12:42

1 Answer 1

Create a trigger that on INSERT will populate that filed.

You cannot have it as a calculated column, as latter cannot refer to any tables.

share|improve this answer

Your Answer

 
discard

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.