重命名索引并在同一事务中询问 Sys.Indexes 中的新名称
我正在尝试研究我们程序中的升级组件。 这需要更改索引的名称
EXEC sp_rename N'Sig_Summary1Index.IX_Sig_Summary1Index_StartTime',
N'Sig_Summary3Index.IX_Sig_Summary1Index1_StartTime', N'INDEX';
（我们需要与 SQL SERVER 2005 保持兼容）
，然后检查它是否存在于同一事务中：
IF NOT EXISTS
( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[dbo].[Sig_Summary1Index]' )
AND name = N'[IX_Sig_Summary1Index1_StartTime]')
CREATE CLUSTERED INDEX [IX_Sig_Summary1Index1_StartTime]
ON [dbo].[Sig_Summary1Index] (
[StartTime] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95
)
ON [PRIMARY]
这将尝试创建索引。 :( 不知何故，在事务期间，sys.indexes 表尚未更新。 有没有其他方法可以避免创建这个索引？
编辑：有什么方法可以“刷新”系统表来更新吗？手动插入记录？做些什么以使“支票”不会被更改？
I am trying to work on the upgrade component in our program.
This requires changing an index's name
EXEC sp_rename N'Sig_Summary1Index.IX_Sig_Summary1Index_StartTime',
N'Sig_Summary3Index.IX_Sig_Summary1Index1_StartTime', N'INDEX';
(we need to remain compatible with SQL SERVER 2005)
and then check if it exists in the same transaction:
IF NOT EXISTS
( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[dbo].[Sig_Summary1Index]' )
AND name = N'[IX_Sig_Summary1Index1_StartTime]')
CREATE CLUSTERED INDEX [IX_Sig_Summary1Index1_StartTime]
ON [dbo].[Sig_Summary1Index] (
[StartTime] ASC
)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95
)
ON [PRIMARY]
This WILL try to create the index. :(
somehow, during the transaction the sys.indexes table is not updated yet.
Is there any other way to avoid creating this index?
Edit: Is there any way i can "flush" the sys tables to update? Insert the records manually? do something so that the "check" will not be changed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
sys.tables 始终与 CREATE INDEX 完美同步。如果您的查询没有找到，则意味着它查找了错误的索引名称。如果您在一个事务内运行 CREATE INDEX，然后在提交之前，您从另一个事务中查找 sys.tables，那么您将陷入普通的读隔离级别，并且直到以下时间您才会看到新索引：事务提交。这是预期和期望的行为。
sys.tables is always in perfect synch with CREATE INDEX. If your query doesn't find it means it looks for the wrong index name. If you run the CREATE INDEX fron inside a transaction and then, before commit, you look in sys.tables from another transaction, you fall into ordinary read isolation levels and you won't see the new index until the transaction commits. Which is the expected and desired behavior.
我倾向于检查旧名称的索引是否存在，如果确实删除它，GO
使用新名称重新创建索引，GO。
更简单。
I tend to check if the index exists with the old name, if it does drop it, GO
Re-create index with new name, GO.
It's simpler.