SQL Server - Automatically Calculate total every day

Question

I need to keep a daily statistic of the count of records in a table.

Is there a way to automate counting the records daily and writing the result into another table? Maybe using a SQL Agent Job or something like that?

I'm using SQL Server 2008.

Thank you!

Edit: If I delete today all records from 1/1/2010, the statistic still needs to show that at 1/1/2010 there were 500 records at the end of the day. So solely using GetDate() and summing up doesn't work, as I'd get 0 records with that method for 1/1/2010.

Answer 1

Add a column to your table like so:

ALTER TABLE My_Table
ADD insert_date DATETIME NOT NULL DEFAULT GETDATE()

You can then query against that as SQL intended.

Answer 2

SQL Job is good -- yes.

Or you could add a date column to the table defaulted to GETDATE(). This wouldn't work if you don't want your daily counts to be affected by folks deleting records after the fact.

Answer 3

In my opinion you answered your own question with the best option. Create a Job that just calls a stored procedure getting the count and stamping them.

The other option mentioned by Tom H. is a better choice, but If you can't alter the table for whatever reason the job is a good option.

Another option could be to place an insert trigger on that table to increment a count somewhere, but that could affect performance depending on how you implement it.

Setting up the job is simple through the SQL Management studio interface with a schedule of how often to run and what stored procedure to call. You can even just write the command directly in the command window of the step instead of calling a sp.

Answer 4

Insert trigger: update counting table record for today (insert if not already created)
Delete trigger: decrement counting table record for today (insert if not already created)

Answer 5

Tom's answer with OMG_Ponies' addendum about tombstoning instead of deleting is the best answer. If you are concerned about how many records were in the table on a certain day, there is a good possibility that someone one day will ask for information about those records on that day.

If that is a no go, then as others have said, create a second table with a field for the PK of the last record for the day, and then count for the day, then create a job that runs at the end of each day and counts all records with OrginalTable.PK > MAX(NewCountTable.Last_PK_Field) and adds that row (Last_PK_Field, Count) to the NewCountTable.