Dropping and adding the Unique Nonclustered Index

Question

Dropping and adding the Unique Nonclustered Index

ramana3327

SSCoach

Points: 19722
More actions
January 14, 2015 at 2:44 pm

#293794

Hi,
I have a index on a column. That index is unique nonclustered index.
After recreate that I want the same name as previous
When I use the script index as drop and create to , it is not giving the same name

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 1

ramana3327 (1/14/2015)
Hi,
I have a index on a column. That index is unique nonclustered index.
After recreate that I want the same name as previous
When I use the script index as drop and create to , it is not giving the same name

The name of an index is not optional in CREATE INDEX, which means that you're using some other method. Please post the code that you're using to drop and create the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 399047 More actions · Answer 2

I'm with Jeff here. I tried a couple of experiments to see if I could break this. Nothing obvious worked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ramana3327 SSCoach Points: 19722 More actions · Answer 3

The Index is system generated one.

It gave me below syntax

ALTER TABLE [dbo].[Test] DROP CONSTRAINT [UQ__Test__BED14FEE58EB02C0]

GO

ALTER TABLE [dbo].[Test] ADD UNIQUE NONCLUSTERED

(

[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) ON [PRIMARY]

GO

Grant Fritchey SSC Guru Points: 399047 More actions · Answer 4

Modify the syntax so that it includes the name you want:

ALTER TABLE [dbo].Mytable ADD CONSTRAINT xxx UNIQUE NONCLUSTERED

(

[ID] ASC

)

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ramana3327 SSCoach Points: 19722 More actions · Answer 5

ramana3327

SSCoach

Points: 19722

January 14, 2015 at 4:06 pm

#1770295

Thank you. It is working

Grant Fritchey SSC Guru Points: 399047 More actions · Answer 6

Good to hear it.

Personal opinion, avoid system generated names. They just make everything harder.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dropping and adding the Unique Nonclustered Index

Cookies on SQLServerCentral