SQL Server（2005） - “已删除” DATETIME和索引 [英] SQL Server (2005) - "Deleted On" DATETIME and Indexing
问题描述
我有一个与数据库设计有关的问题。我正在使用
的数据库要求以某种方式处理数据，使其永远不会被物理删除。我们开始在向某些表添加DeleteDateTime列的路径上进行
，默认情况下为NULL，但是一旦标记为
就会将记录标记为已删除。
I have a question related to database design. The database that I'm working with requires data to treated in some way that it is never physically deleted. We started going down a path of adding a "DeleteDateTime" column to some tables, that is NULL by default but once stamped would mark a record as deleted.
这使我们能够轻松归档我们的数据，但我仍然觉得在一些区域的黑暗中，特别是
是否会被认为符合最佳实践以及如何有效地索引这些表。
This gives us the ability archive our data easily but I still feel in the dark on a few areas, specifically whether this would be considered in line with best practices and also how to go about indexing these tables efficiently.
我将举例说明：我们有一个名为Courses的表，其中包含一个复合主键列SiteID和CourseID。
此表还有一个名为DeleteDateTime的列，根据我上面的描述使用。
I'll give you an example: We have a table called "Courses" with a composite primary key made up of the columns "SiteID" and "CourseID". This table also has a column called "DeleteDateTime" that is used in accordance with my description above.
我无法使用SQL Server 2008过滤后的视图功能，因为我们必须与
SQL Server 2005兼容。我应该在此表的聚集索引中包含DeleteDateTime吗？如果是这样的话，它应该是
索引中的第一列（即DeleteDateTime，SiteID，CourseID）......
I can't use the SQL Server 2008 filtered view feature because we have to be SQL Server 2005 compatible. Should I include "DeleteDateTime" in the clustered index for this table? If so should it be the first column in the index (i.e. "DeleteDateTime, SiteID, CourseID")...
有没有人有任何理由我为什么应该或不应该遵循这种方法？
Does anyone have any reasons why I should or shouldn't follow this approach?
谢谢！
推荐答案
您是否有可能将这些死记录转移到单独的表中？例如。对于课程表，有一个 Courses_deleted 表或类似的东西，具有相同的结构。
Is there a chance you could transfer those "dead" records into a separate table? E.g. for your Courses table, have a Courses_deleted table or something like that, with an identical structure.
当你删除一条记录时，你基本上只是把它移到死表。这样，你实际的当前数据的索引保持小而且活泼....
When you "delete" a record, you basically just move it to the "dead table". That way, the index on your actual, current data stays small and zippy....
如果你需要一个聚合视图，你总是可以定义一个 Courses_View 将两个表联合在一起。
If you need to have an aggregate view, you can always define a Courses_View which unions the two tables together.
真实桌上的聚集索引应该是因为小，静态，恒定和可能，所以我肯定不建议将这样的日期时间列放入其中。不是一个好主意。
Your clustered index on your real table should be as small, static and constant and possible, so I would definitely NOT recommend putting such a date time column into it. Not a good idea.
有关如何选择一个好的群集密钥以及它需要什么的绝佳信息，请查看Kimberly Tripp的博客条目：
For excellent info on how to choose a good clustering key, and what it takes, check out Kimberly Tripp's blog entries:
- GUIDs as PRIMARY KEYs and/or the clustering key
- The Clustered Index Debate Continues...
- Ever-increasing clustering key - the Clustered Index Debate..........again!
Marc
这篇关于SQL Server（2005） - “已删除” DATETIME和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
