December 21, 2016 at 9:28 am
I should probably know this but would like confirmation. This unique index is not being used for reads and that has been the case in QA and prod for quite a while. It does not show up in the constraints section under the table, just under indexes. Even in QA where it has been a disabled index for months, I find no duplicates on the two columns concerned. Scripting it out produces an "add constraint" script. Am I safe in disabling it?
-- these are unique, even in QA where the unique index has been disabled
select subscription_id,event_id,count(*) from event_dispatch
group by subscription_id,event_id having count(*)>1 order by subscription_id,event_id
ALTER TABLE [dbo].[EVENT_DISPATCH] ADD CONSTRAINT [EVENT_DISPATCH_UC1] UNIQUE NONCLUSTERED
(
[SUBSCRIPTION_ID] ASC,
[EVENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
December 21, 2016 at 9:51 am
Indianrock (12/21/2016)
I should probably know this but would like confirmation. This unique index is not being used for reads and that has been the case in QA and prod for quite a while. It does not show up in the constraints section under the table, just under indexes. Even in QA where it has been a disabled index for months, I find no duplicates on the two columns concerned. Scripting it out produces an "add constraint" script. Am I safe in disabling it?
-- these are unique, even in QA where the unique index has been disabledselect subscription_id,event_id,count(*) from event_dispatch
group by subscription_id,event_id having count(*)>1 order by subscription_id,event_id
ALTER TABLE [dbo].[EVENT_DISPATCH] ADD CONSTRAINT [EVENT_DISPATCH_UC1] UNIQUE NONCLUSTERED
(
[SUBSCRIPTION_ID] ASC,
[EVENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
How are you looking for the constraint, through Management Studio or querying the metadata? If looking through Management Studio, the unique constraint will show up in the Keys folder, not the Constraints folder. To look at it as a query try something like:
SELECT * FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.EVENT_DISPATCH')
December 21, 2016 at 10:05 am
Yes it's there under keys and in your query results. So apparently I can disable the unused index and the constraint ( key ) remains in force.
name type_desc
EVENT_DISPATCH_UC1UNIQUE_CONSTRAINT
December 21, 2016 at 10:59 am
Indianrock (12/21/2016)
Yes it's there under keys and in your query results. So apparently I can disable the unused index and the constraint ( key ) remains in force.name type_desc
EVENT_DISPATCH_UC1UNIQUE_CONSTRAINT
no, SQL Server uses the index to enforce the unique constraint so you can't disable it without affecting the constraint as well.
December 21, 2016 at 11:09 am
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply