Is it possible to apply default filter on a table in SQL Server?

Question

I have a table named Users(for example), and it has 3 columns(Id, Name, and IsDeleted), IsDeleted indicates the user is deleted, or not?

When i run below select statement(select * from users), is it possible to filter out the records which has IsDeleted equals 1, automatically? I have a database which designed as soft delete only, so there are many table with this IsDeleted column. when query database, we have to add where clause to filter these records out, it's very annoying, especially when query/join multiple tables. I want ask here, is there some feature(like default filter?) to do this. so,the deleted records can only be queried, when the table default filter is disabled.

Answer 1

A clean and easily understandable way to achieve this is with views. You could create a view that filters out users by this flag and returns all columns.

CREATE VIEW v_Users AS
    SELECT * FROM Users WHERE IsDeleted = 0

Then in all your queries you can use this view instead of the table.

SELECT u.email, u.name, likes.*
FROM v_Users AS u
INNER JOIN likes ON likes.user_id = u.id

Full example fiddle: http://rextester.com/QDN58706