Logical deletion of SQL Server records

1

We have the culture to only make logical exclusions in the application, maintaining 2 fields where 1 is the deletion date and another one that receives 'S' or 'N'.

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

select * from tb where exc = 'N'

Given that we have 2 fields to indicate the same thing (whether it is deleted or not), my question is:

What is more performative? Make the query as it is already being made or evolve to something like this:

select * from tb where dat_exc is not null

The goal is to keep the delete date only.

    
asked by anonymous 13.02.2015 / 00:52

1 answer

2

This kind of thing is hard to answer at all. Every situation is different. Even in your application what may be good today may not be tomorrow due to a hardware change, database version, usage pattern, etc. Just measuring to tell you the truth. So as any measurement that I do is not going to server you nor will I waste my time.

You probably want to know what intuitively performs best. So I'll say that's the way you're doing. Comparing a character tends to be faster than comparing a date that in addition to being a larger die 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 to be wrong. So I can only reinforce it to test both.

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

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

Perhaps in your case is not so necessary and the difference is so small that you should not even worry. If you have some bottleneck you are probably somewhere else. And if you have not, the concern is exaggerated. Think about it.

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

    
13.02.2015 / 01:07