Why recreate instead of rename?

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Why recreate instead of rename?

Postby Fenske » Thu Dec 22, 2011 10:42 am

Dear Support,

i have a question regarding the generated syncronizing script.

I have an example:
Code: Select all
left side: CREATE NONCLUSTERED INDEX [Name_Vorname] ON [dbo].[Inter] ([Name], [Vorname])

right side: CREATE NONCLUSTERED INDEX [Name] ON [dbo].[Inter] ([Name], [Vorname])

The resulting synchronisation script does the following:
Code: Select all
DROP INDEX [Name] ON [dbo].[Inter]
CREATE NONCLUSTERED INDEX [Name_Vorname] ON [dbo].[Inter] ([Name], [Vorname])

My question is: Wouldn't it be wiser (and a lot faster) if the alteration script only renames the index as all other parameters are equal?

It's kind of a showstopper to us as we need to synchronise very large tables and recreating doesn't qualify for the perfect solution.
Posts: 2
Joined: Thu Dec 22, 2011 10:25 am

Postby james.billings » Tue Dec 27, 2011 12:42 pm

Thanks for your post. This same question was actually asked back in 2005 (ref. SC-1308) and the request was closed, with a comment of:

No plans to do this

While indeed it would be more efficient from SQL Server's point of view, because of sp_rename it would confuse system tables, and it would NOT be more efficient in SQL Compare (indeed it would be much more inefficient)

I'm not sure of any more detailed reason as to why it would be more inefficient in SQL Compare unfortunately as that's all that was written on the request, but it sounds like performing the rename could actually make the problem worse and we have no plans to fix it.
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby Fenske » Tue Dec 27, 2011 5:06 pm

Thank you for your reply.

With kind regards,
Posts: 2
Joined: Thu Dec 22, 2011 10:25 am

Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 2 guests