Count even deleted rows

Question

Is it possible to count even deleted rows in a SQL Server table?

Let's say we have a table Contacts.
I'll add 5 new contacts, and hit count on it. It should return value of 5.
Now, when I delete a contact and run the count it will return 4.

Since I'm working on an app who generates a unique number (Count-year-NP) from the count, it's crucial for me that it's universal.

I don't just count the database, my query is:

SELECT COUNT(*) FROM some_table WHERE YEAR(now) = YEAR(GETDATE())

It counts data where year is current.

Is there any way using stored procedure or simillar, just to keep the count universal for deleted and inserted rows?

I was thinking of marking rows as deleted instead of deleting them, but I want to be able to delete the values.

Answer 1

When you need only information about number of inserted rows and not about record's data - you could use the approach, which I present below.

Of course, you could mark rows as deleted but it will require changes in data querying. Also it will have impact on resources (storage, maybe views, probably additional indexes, CPU when querying data).

Solution

Create a table which store counters for all of the tables you are interested in. In your case you should store counters per year for those tables.

Update those counters when inserting new records and read from counters table when need to generate new unique number.

Example of a counter table:

CREATE TABLE Counters (TableName NVARCHAR(256), [Year] INT, [Value] BIGINT)

Remarks

Counter updates could be achieved by triggers in easy way but you should be aware of performance and concurrency issues. Consider updating counters table through application layer or stored procedures, which should be a better way.

Answer 2

_{Community wiki answer:}

You could create a DELETE TRIGGER on that table to capture the row(s) and the date deleted and store that information in a history table. Then UNION the base table and the history table (perhaps using a view) to get your results.

You may need additional logic in triggers if the "same row" (identified by some key) could be inserted and deleted many times, and you want to count all those actions as just one change. The question does not specify whether that would be a concern or not.