In all tables I have three fields:
DelFlag -> Set to 1 when record is deleted.
DelU -> User ID who deleted the record.
DelT -> Time of deletion.

All queries include WHERE DelFlag = 0, but optionally of course you can also query the deleted records. This functionality very often saved my day, I am so glad we have it and I could not imagine to just delete the records for good.

Of course you can later decide to delete the records at any time, as we used to do in Foxpro with the PACK command. And you can restore as well.

Not all software engineers are automatically practical thinking people, but this is undoubtedy a very practical solution and closely related in the real world.

>Hello
>Is a good practice to put a state field in a table when using sqlserver tables?, so if it is 1 means active, 0 is deleted, so i can recover the record if it is necesary?
>Thank you in advance
>Luis Martin
>
>P.D. When i ask it in sql central some , take advantage of the oportunity to laugh at vfp for having logically delete option
>http://www.sqlservercentral.com/Forums/Topic1219862-391-1.aspx
>
>
>one said:
>foxpro and vfp??? Is that like calling C++ and VCC different languages??? FoxPro and Clipper were both nothing more than a compiler for DBASE. There are a number of reasons that DBASE is no longer popular.
>
>I have also worked on a system with logical deletes and it is a PITA!!!
>
>
>other said:
>Because they didn't know how to delete the rows without messing up the underlying files. They had a special job to go through later to actually delete the rows. DBase II and DBase III worked the same way. It simply took them too long to delete rows. It was easier for them to mark them as deleted.
>
>Shifting gears, I almost never delete from a table. I'll create a TYPE 2 slowly changing dimension or I'll create an archive table, but I almost never do an actual delete.
>
>The reason why a "state" column of "IsActive" sucks so bad is because 1) it has very low uniqueness and is a terrible index candidate and 2) if give you no idea when the row became inactive. If you want a "state" column, do the right thing and create 2 columns... DateActive and DateInactive. If you read up on Type 1 slowly changing dimensions, you migh be able to get away with just one column but make it a datetime column.