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.