How to Count Even Deleted Rows in SQL Server

countdeletesql serversql server 2014

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.

Best Answer

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.

Related Solutions

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

Is this normal for a forced join order to make the query estimates to be completely inaccurate (and thus query times unpredictable)?

The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.

Should I just expect that I'll have to either accept sub-optimal query performance, or watch it like a hawk and frequently manually edit query hints? Or maybe hint every join as well? .3s to 2s is a big hit to take.

Preferable would be to ensure the optimiser is given the information it needs to generate the best plan, without using the FORCE ORDER hint. By doing so, it should cope better with changes to the underlying data distribution without requiring manual intervention. That said, if the nature of the data is such that cardinality could vary significantly hour by hour or day by hour, consider using a plan guide to ensure the plan is fixed.

Is it obvious why the optimizer blew up after deleting rows? For example, "yes, it took a sample scan, and because I archived most of the rows earlier in the data history the sample yielded sparse results, so it underestimated the need for a sorted hash operation"?

You didn't mention the row counts in the problem tables but it's likely that the deletions either:

didn't remove enough rows to to trigger a statistics update. This should occur when 20% of rows have been modified but there is the option to use trace flag 2371 to enable a dynamic threshold.
did trigger a statistics update but the sample gathered wasn't representative. Correct this by running a manual update WITH FULLSCAN.

You could also be running into good old fashioned parameter sniffing problems, for which there are myriad options to work around. WITH RECOMPILE might be an expensive option to specify with a query this large but it's worth investigating at both procedure and statement level.

SQL – How to Efficiently Count Rows by Limiting Found Rows

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)

Best Answer

Related Solutions

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

SQL – How to Efficiently Count Rows by Limiting Found Rows

Related Question