Renaming indexes
-
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?
-
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
-
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 -
Sorry for posting this again. Let me take the example of Northwind database.
Stored procedure:CustOrdersOrders
Table
rdersIndex 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
-
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.
-
This is one reason among many that I am vehemently opposed to the use if INDEX hints in SQL Statements.
-
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 -
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..
Sun August 22, 2004 11:25 PM (NEW!) Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply