Can you tell SQL Server not to return specific rows?

Question

We have a database where contacts are only soft-deleted, i.e. we set a bit named deleted to true. The API handles this, but sometimes you need to run queries directly against the database and it's easy to forget that bit, and export contacts that are actually deleted. It's not a big problem, because I'm used to the system, but if I'm on holiday, or if someone else is to take over I'd like to make sure these rows are not returned - unless specifically asked for. This leaves us with the following two questions:

1. Is there a way to tell SQL Server (2005 in this case) not to return rows if for example a bit is set to true?

2. If so, can these rows still be retrieved if specifically asked for (where bit = 'true')?

Answer 1

In order to select either the non-deleted rows or the deleted ones, create two views:

CREATE VIEW ActiveContacts
AS 
   SELECT (list of fields)
   FROM dbo.Contacts
   WHERE deleted = 0  

CREATE VIEW DeletedContacts
AS
  SELECT (list of fields)
  FROM dbo.Contacts
  WHERE deleted = 1

Now, you can easily just pick from either the active or the deleted contacts:

SELECT (list of fields)
FROM ActiveContacts

SELECT (list of fields)
FROM DeletedContacts

That way, you can easily encapsulate that selection criteria and if you don't return that "deleted" flag in your view, outside users won't even see that flag and won't know it's there.

Marc

Answer 2

You could create a view that hides those rows, and if you need the rows directly, you can still go to the table. Try CREATE VIEW vwBlah AS SELECT table, columns, go, here FROM TableName WHERE flag = 0.

Answer 3

The only way that I can see to automatically do this for you would be to create views on the relevant tables that specifically exclude the deleted rows. Then use the real tables when you need to see the deleted data.