每个具有 IsActive 字段的表都附有视图，这是否很常见？
我有几个具有 IsActive 列的表，该列指示记录是活动的还是已删除。这些表的使用相当频繁。
为每个表创建一个视图并仅选择 IsActive 为 true 的记录是否很常见？或者这太过分了？
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 for each of these tables and select only the records where IsActive is true? Or is this overkill?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
视图只是一个存储的查询 - 无论您在视图中还是在
where子句中检查 isActive，它都会以完全相同的方式执行。如果您发现自己更频繁地使用 isActive 行，请查看 过滤索引。例如，我们有一个票务系统，其中 99% 的活动与开放票证相关。通过仅为活动票证添加过滤索引，我们能够极大地提高性能。
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
whereclause.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.
与其说是过度杀伤，不如说是有问题的设计。
该方法通常被称为“软”删除，其中记录被标记为特定状态以控制其可见性。这会占用空间，但可以轻松恢复记录 - 否则您只能从备份（或更糟 - 事务日志）中恢复。
我不会使用视图来查看表本身中应该包含的内容。非物化视图只是一个宏，它用它封装的查询替换视图引用——对于给定查询中的每个实例。这样做会带来轻微的性能优势，因为底层查询可能会被缓存。物化视图（SQL Server 术语中的索引视图）比非物化视图更好，但您可以通过仅对状态列建立索引来实现相同的效果（2008 年以上，可能需要考虑过滤索引）。在实际的表格中也没有混乱。这取决于，所以你最终必须测试并确定根据数据和数据亲自查看设置。
根据状态对原始表进行分区是提高查询性能的另一种可能性，但仍然需要在查询中进行规范。
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.
不过，也许值得为视图建立索引。这取决于活动/非活动划分。
非索引视图只是一个可扩展的宏，并且总是很容易添加 JOINS，当您将视图连接到视图等时，这会提高性能
并且使用 SQL Server 2008 在 IsActive 上添加过滤索引
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