Is it common to accompany views for each table that has a IsActive field?

I have several tables that have IsActive column, which indicates whether a record is active or deleted. These tables are used fairly frequently.

Is it common to create a view开发者_如何学Go for each of these tables and select only the records where IsActive is true? Or is this overkill?

A view is just a stored query-- it will execute precisely the same way whether you check for isActive in the view or in the where clause.

If you find yourself using the isActive rows much more often, have a look at filtered indexes. For example, we have a ticketing system where 99% of the activity is related to open tickets. We were able to improve performance greatly by adding filtered indexes for the active tickets only.

Is it common to create a view for each of these tables and select only the records where IsActive is true? Or is this overkill?

Not overkill so much as questionable design.

The approach is often referred to as a "soft" delete, where the record is flagged with a particular status to control its visibility. This eats space, but allows for easy restoration of records -- otherwise you're left with restoring from backups (or worse -- transaction logs).

I wouldn't be using a view for what should be in the table itself. A non-materialized view is just a macro that replaces the view reference with the query it encapsulates -- for every instance in a given query. There's a slight performance benefit from this because the underlying query is likely to be cached. A materialized view (indexed view in SQL Server terminology) would be a better idea than a non-materialized view, but you could do the same with just indexing the status column (2008+, a filtered index might be a consideration). in the actual table too without the clutter. It depends, so you ultimately have to test & see for yourself based on the data & setup.

Partitioning the original table based on the status is another possibility for query performance, but still requires specification in a query.

Probably, it'd be worth indexing the view though. It depends on the active/inactive split. A non-indexed view is just a macro that expands though, and its always tempting to add JOINS which bollix performance when you join view onto view etc

And with SQL Server 2008 adding a filtered index on IsActive