How to modify index type in SQL Server?

Question

I need to change the type of index from "Unique Key" to "index". I`ve spent 2 days trying to drop and recreate it. Unfortunately, there are a lot of dependencies and the only way to fix my issue is to modify the existing index. SSMS visual editor allows me to change type however I ought to find out how to make it programmatically.

It is really bad idea to use something like UPDATE sys.key_constraints SET type = 'UQ' WHERE ... Such a solution is not working properly.

I would appreciate any help!

I removed the PS as the question in it is off-topic for Stack Overflow. — TT., Jun 05 '20 at 16:50
Yes, it is a really bad idea to run an update against a sys table. — dfundako, Jun 05 '20 at 16:59
You can do it in management studio, but instead of saving it, you can generate a script. — Wouter, Jun 05 '20 at 16:59

score 2 · Answer 1 · answered Jun 05 '20 at 16:56

2

To implement such change you will need to drop and recreate the index.
There is no alter statement that would change it.

answered Jun 05 '20 at 16:56

Piotr Palka

3,086
1
9
17

Thank you for your answer @Piotr! Unfortunately, it is impossible to DROP this index before I`ll manually change the type using SSMS visual tools. Is there any way to run "FORCE" dropping query? – Vlad Rovner Jun 05 '20 at 17:01
What do you mean? - please post the error you are getting. – Piotr Palka Jun 05 '20 at 17:02
`The explicit DROP INDEX statement is not valid in the "controller.IX_controller" index. It is used to enforce the UNIQUE KEY constraint`. – Vlad Rovner Jun 05 '20 at 17:09
You need to drop UNIQUE constraint before dropping the index. This constraint wouldn't be valid without unique index. – Piotr Palka Jun 05 '20 at 17:14

score 0 · Answer 2 · answered Jun 05 '20 at 17:21

It is possible to drop the index as a constant:

BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.table_name DROP CONSTRAINT IX_CONSTAINT_NAME GO

How to modify index type in SQL Server?

2 Answers2