Problem solve Get help with specific problems with your technologies, process and projects.

Automatically incrementing a char field

I would like to be able to automatically increment a char field that is based upon two combined numbers. For example, the field value would be entered as 0001-04; 0001 would be like generating an integer number automatically where the next field represents the current year (2004). How this would work within the application is that each time an entry is made, it would automatically generate the next number when an entry is made. So, if the user begin entering date on January 1st of 2004, the number would display as: 0001-04, the next number 0002-04 and so on. When the last entry is made on December 31, 2004 and we move to January 1, 2005, the user would like the application to automatically generated the number to read 0001-05. Can you provide an example of a trigger or stored procedure that can accomplish this task?

Based on the information, I would recommend creating a single column on the pertinent table to serve as an identity that SQL Server will automatically generate the needed value for you to increment the final value by one for each transaction in the table. This column may also be a good candidate to serve as the primary key with a clustered or non-clustered index. Then create a second column to store the incremented number and date. Then use the combination of the GETDATE() and DATEPART functions. When you insert a new record in the table, set a default on the column where you want to store this value to capture the identity value, a dash and the two-date year.

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.