Solved

Are "deleted" records in a SQL database customarily reported with active data?

Posted on 2013-06-14
6
290 Views
Last Modified: 2013-06-15
I run a third-party CRM that has been giving us "unexpected" results in our searches.  I've tracked this down to the vendor including RECYCLE data in those search results.  Specifically, if a record is deleted it is put into Recycle bin.  If record remains in Recycle bin, it then is included in query results.  The "recycle records" aren't marked as pending deletion which wouldn't make it right but would make it "explainable" to my users who think delete means GONE.

Vendor argues this is normal. I don't think so... do you?  I welcome both developer-level and regular-end-user perspective.  Brisk, direct and simple replies especially prized.

I cannot turn off Recycle feature nor is there an "auto-empty Recycle bin" option, nor is there a "Delete=Recycle/Keep Active" or "Delete=Delete I Really Mean It" option for users.

I would like some community feedback on whether or not I am correct that the occasional opportunity to restore a record is not worth having inaccurate search results and this feature should be eliminated or better controlled.

Thanks!  I'd like to split points like this:

First response = 200
Second and Third  response = 150 each

Everybody else who chimes in:  thanks and many good graces.
0
Comment
Question by:ColdKathleen
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39249584
What crm package?
0
 

Author Comment

by:ColdKathleen
ID: 39249680
CRM detail not relevant to "concept" question, no need to bash the vendor by name... Looking to establish the baseline of deleted data being no longer available to queries.  It seems obvious to me as long-time db user/admin BUT since I am not a developer I needed a true tech's perspective.  Points still available.
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 250 total points
ID: 39249874
This isn't a SQL Server or Database Issue, if i delete records at database level they are GONE..!!!
The Recycle bin is a capability your CRM. The Vendor's argument "this is normal" is correct. They Programmed the CRM so you can recover your records if someone make a mistake and delete them.
I run a third-party CRM that has been giving us "unexpected" results in our searches.
If you mean, by running SELECT SQL-Statements then look in your table again, i'm 99% sure you will find a Column called "deleted" or something like to identify "Deleted Records". In this case you need to include it in your queries.

obviously makes sense to add a option "empty recycle bin" ...   ;)

Regards
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 250 total points
ID: 39250016
As this is heavily related to the CRM package, knowing the type of CRM package it is may indeed help. If you delete a record from a database it is gone.

In Sage CRM for example, records are not deleted rather they are marked as deleted so they are not visible to the program despite there not being a mechanism in the package to restore deleted records. Onyx CRM (if it is still arond) employs a similar mechanism. Each record has a record status.

I would seriously question the usefulness of a CRM package if it included "deleted" records in its search results. If the search is designed to run on records, it could easily be modified to exclude deleted records. If the records are in a recycle bin rather than marked as deleted, then the system would have actively been programmed to include deleted records which seems stupid to me. Even more stupid would be to not program in an override to that silly set up in the first place.
0
 

Author Closing Comment

by:ColdKathleen
ID: 39250600
Thanks for the input.  When a vendor defends a nonsensical function at the expense of the "end user experience" (end user thinks deleted data is gone) then I like to step back and check my own presumptions.  

There IS an "IsDeleted" field but that's a bit esoteric for every-day users on every search every time.  In fact, I figured the vendor was using that for processing searches if they knew they were folding in Recycle records.  

I've firmed up my understanding of "common practice" which is what I love about this particular forum.

ColdKathleen
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39250610
Glad to have been of some help :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now