alex_fajardo2002 (2/3/2016)
Hugo Kornelis (2/3/2016)
So you want to add a generated column that can be combined with the Year column to become a composite candidate key? Okay."How do I do this in SQL Server?"
Run a query to find the highest value for the current year, add one, don't forget to use COALESCE to handle the case when no rows exist yet for the current year.
Also, talk with the business about the handling of gaps (if data is deleted). And be aware that this smght cause blocking if you have a high frequency of inserts,
I would rather not do this by query but I want a column to have such data
Yes, I know. Add the column to your table. Then use the code I posted is what you add to your INSERT statements to generate the data for this column.
If you have multi-row inserts, then you may may have to use ROW_NUMBER and add that to the result of the subquery to ensure that each new row gets a unique value.
"Also, what would you call YearID column in this case?"
I would call it whatever the business calls it.
For instance, the bookkeeping regualtions of my country give companies two choices for invoice numbers: either use an invoice number that is unique over all invoices ever issued by the company, or use an invoice number that is unique in the current year. In the latter case, invoice numbers can restart when the year rolls over. So in this case, the number that restarts at 1 in every new year would be called InvoiceNumber.
What do the business users of your database call the number you are adding? That's your column name.
Sorry, Wrong question, I would like to ask what is the sql, it term for such a column,
Eehhrrmm, ... a column?
Maybe I am misunderstanding you. There are no column names in SQL Server that have any special meaning. (Except "timestamp")
If you are looking for the name of the feature that automatically generates the numbers in this fashion for you, then there is no name for it since there is no such feature. But as I already showed, it's easy to roll your own.