Renaming indexes

Question

Renaming indexes

Roger Falor

Old Hand

Points: 392
More actions
August 23, 2004 at 12:43 am

#86333

Sun August 22, 2004 11:25 PM (NEW!)
I have a need to change index names. I am dropping the index then re-adding them.
Do I need to recompile procedures, views and/or triggers to use the new index?

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

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

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

Hi Roger,

1. When you drop the index name, SQL Server would throw an error saying there are dependencies for this index, provided if you have explicitly named the index in your stored procedures,view and triggers.

2. When you drop the index name and any of the index name is not explicitly used by any your other database object. Then no probs you can continue your plan.

As SQL Server uses the cost based execution plan. It will use the required and optimized index.

Thanks,

Ganesh

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Roger,

No you don't need to recompile your code like Ganesh stated. However, more than likely the server will recompile all of the needed code by itslef due to the fact that indexes did change. You may notice a slight perf. hit the 1st time the functions are called (just like a server recycle).

Should not be a problem...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 3

Sorry for posting this again. Let me take the example of Northwind database.

Stored procedure:CustOrdersOrders

Table rders

Index Name :CustomerID

Changed Index Name :MyCustomerID

Let us consider the stored procedure looks something like this.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)

AS

SELECT OrderID,

OrderDate,

RequiredDate,

ShippedDate

FROM Orders with (index = CustomerID)

WHERE CustomerID = @CustomerID

ORDER BY OrderID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

*****************************************************

Pls note the index "CustomerID" used in the select list

*****************************************************

Then issue the statement to rename the index

EXEC sp_rename 'Orders.[MyCustomerID]', 'CustomerID', 'INDEX'

You will get a message

Caution: Changing any part of an object name could break scripts and stored procedures.

The INDEX was renamed to 'CustomerID'.

When you try to execute the stored procedure, you will end up with the following errors.

Server: Msg 308, Level 16, State 1, Procedure CustOrdersOrders, Line 5

[Microsoft][ODBC SQL Server Driver][SQL Server]Index 'CustomerID' on table 'Orders' (specified in the FROM clause) does not exist.

This is what I meant, we need to recompile the stored procedure if the index is explicitly used anywhere.

Ahrens,

Pls correct me if Iam wrong in my understanding.

Thanks,

Ganesh

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

The original index name was CustomerID in order table in Northwind database. Once you change it, for example, to MyCustomerID with sp_rename, You have to modify your sp to use new index MyCustomerIDtoo because it used index hint to point to original index CustomerID before.

Mark Khoury SSC Enthusiast Points: 139 More actions · Answer 5

This is one reason among many that I am vehemently opposed to the use if INDEX hints in SQL Statements.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 6

Ganesh,

Allen is correct. I apologize if I misunderstood. IF you change the name of an index that is explicitly called then you need to change your code.

It is just like a table name or a column name, etc..

One thing you could do is look through syscomments and see if the index name that you are thinking about renaming is in code somewher on your server. This won't help with applications, DTS packages, etc...

As a general rule I try and not FORCE the index but, sometimes you don't have a choice

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Tiago Rente Default port Points: 1403 More actions · Answer 7

Hi,

Before working with SQL Server I worked with ASE (Sybase 11 and 12), my experience with ASE advise my to force indexes when you know that it is better knowing the data. Or, when you compile a SP with an empty table that will grow with time. Up to 1000 rows a table scan is faster then index seek + table access (ASE).

Now in SQL Server, recently I had an experience that in a query joining 3 tables (79 million x 3 million x 3.5 million) we were forcing the indexes and it was taking ~120 minutes. After analysing better the query plan it turned out that the optimizer already used the indexes and without forcing the indexes the query would take < 4 minutes. Those times were consistent with the cost of the query plan.

The problem is that SQL Server would to a very expensive bookmark when you force the index, but will not do it if the optimizer decides to use the index.

To finish, with my experience in SQL Server try to avoid forcing the indexes unless you really know what you are doing and as always test both queries, check the query plan, etc..