在这种情况下我应该使用索引吗?
我是数据库新手,正在解决一个问题。我使用 SQL Server 2005。
我有一个表(审核表),它从另一个表(主表)上的触发器获取数据。审核表上不能有 PK,因为可能存在重复项。它有大约 1500 万行。我正在对其运行更新查询,这导致表扫描。
我想知道如果添加非聚集索引,如何测试它对插入/删除的影响。添加索引后,是否会出现由于服务器忙于重建表/索引而导致来自触发器的数据丢失的情况?
谢谢 维克拉姆
I am new to database and am struggling with a problem. I use SQL Server 2005.
I have a table (Audit table) which gets its data from a trigger on another table (main table). The Audit table can not have a PK on it as there could be duplicates. It has around 15 million rows. I am running an update query on it which is resulting in table scans.
I want to know if I add a non clustered index, how I can test its impact on the inserts/deletes. Adter adding the index, would there be a scenario where the data coming from the trigger will be lost because the server is too busy rebuilding the table/index?
Thanks
Vikram
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您始终可以添加
IDENTITY
列作为主键和聚集索引。由于它是自动递增的,因此索引碎片不会有太多问题。至于由于服务器太忙重建索引而从触发器中丢失数据 - 不,这种情况永远不应该发生。
You can always add an
IDENTITY
column as the primary key and clustered index. Since it's auto-incrementing, you shouldn't have many problems with index fragmentation.As for losing data from the trigger because the server is too busy rebuilding the index - no, that should never happen.