February 16, 2017 at 11:06 pm
I AM HAVING A " UNIQUE NON CLUSTERED INDEX " ASSOCIATED WITH 3 COLUMNS. NOW WE HAVE DECIDED TO REMOVE ONE OF THE COLUMN FROM THE TABLE.
1: WHAT ARE THE THINGS WE NEED TO CONSIDER BEFORE DROPING THIS UNIQUE NON CLUSTERED INDEX.
2: WHAT IS THE SYNTAX FOR DROPING THIS UNIQUE NON CLUSTERED INDEX
PLEASE HELP , THANKS .
February 17, 2017 at 1:45 am
February 17, 2017 at 2:15 am
Clustered index is the table, so you might want to recreate the index without the column, or you will end up with a heap table.
Since it is a Unique index, chances are that it is a key, so be aware of any relations, business rules etc that are impacted.
Apart from above this is a normal operation that you can go ahead and do, take backups and make sure recreating index might require some additional temporary storage, especially if you are having a large table.
February 17, 2017 at 2:32 am
NISHANTHKANNUR - Thursday, February 16, 2017 11:06 PM1: WHAT ARE THE THINGS WE NEED TO CONSIDER BEFORE DROPING THIS UNIQUE NON CLUSTERED INDEX.
No need to SHOUT!
Do you intend to recreate the index after dropping the column from the table? If so, you will need to consider whether the index is still unique without it. If it isn't, you'll either need to find another column that makes the index unique, or recreate the index as non-unique.
John
February 17, 2017 at 2:35 am
joeroshan - Friday, February 17, 2017 2:15 AMClustered index is the table, so you might want to recreate the index without the column, or you will end up with a heap table.
OP asks about a unique nonclustered index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2017 at 2:41 am
GilaMonster - Friday, February 17, 2017 2:35 AMjoeroshan - Friday, February 17, 2017 2:15 AMClustered index is the table, so you might want to recreate the index without the column, or you will end up with a heap table.OP asks about a unique nonclustered index
oops! sorry I read it wrong. Thanks Gail for notifying this.
February 17, 2017 at 2:46 am
John Mitchell-245523 - Friday, February 17, 2017 2:32 AMNISHANTHKANNUR - Thursday, February 16, 2017 11:06 PM1: WHAT ARE THE THINGS WE NEED TO CONSIDER BEFORE DROPING THIS UNIQUE NON CLUSTERED INDEX.No need to SHOUT!
Do you intend to recreate the index after dropping the column from the table? If so, you will need to consider whether the index is still unique without it. If it isn't, you'll either need to find another column that makes the index unique, or recreate the index as non-unique.
John
Sorry for posting in caps
Thanks for the reply. I need to recreate it and it should be unique again. So I will check if it still unique without that column or I will find another column.
Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply