Logical deletion of SQL Server records

Question

We have the culture of making only logical exclusions in the application, keeping 2 fields where 1 is the date of exclusion and another char that gets’S' or 'N'.

When we perform searches for deleted records, we do something like:

select * from tb where exc = 'N'

Since we have 2 fields to indicate the same thing (whether you are excluded or not), my question is as follows:

What is more performative? Make the query as it is already being done or evolve into something of this type:

select * from tb where dat_exc is not null

The goal is to keep only the date of exclusion.

Answer 1

This kind of thing is difficult to answer definitively. Every situation is different. Even in your application what may be good today may not be tomorrow by a change of hardware, database version, usage pattern, etc. Just measuring to be sure. So like any measurement I make it won’t serve you or waste time.

You probably want to know what intuitively performs best. So I’ll say that’s the way you’re doing it. Comparing a character tends to be faster than comparing a date that besides being a larger data will probably need checks (if it is null, for example) and extra conversions. But I lost count of how many times what was intuitive turned out wrong. So I can only reinforce to take the test with both.

I would suggest testing if anything changes to use the column exc as null or have a value. Or change it to a type bit and see what happens in each situation in the main darlings that you use.

These databases have an immeasurable amount of monitoring, statistics, profiling precisely because they are very useful and necessary.

Maybe in your case it’s not so necessary and the difference is so small that you shouldn’t even worry. If you have any bottleneck you’re probably somewhere else. And if you don’t, the worry is exaggerated. Think about it.

Particularly I would choose to have only the date until this proved problematic.