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 to column doc_number have sequential numbers for each tip.
To be clearer I want to automatically or default or computed column whit 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

Can I in MSSQL2005 have column which will automatically have values. Shod this be done by computed column, default (constraint) or trriger ?

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
add comment

1 Answer

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
add comment

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.