Run the script in Enterprise Manager query window with your database selected as the current database.
The result window will contain a set of SP_RENAME commands.
Check these and run them. They will rename your indexes to standardise the names.
According to pattern:
depending on whether the index is unique (AK) or normal (IX).
The script handles any number of columns in an index limited by the maximum size of a name.
-- Index rename
-- According to pattern:
'EXEC sp_rename ''' + object_schema_name(I.object_id) +'.'+ object_name(I.object_id) +'.' + I.name + ''', ''' +
CASE WHEN I.is_unique=1 THEN 'AK_' ELSE 'IX_' END + object_name(I.object_id) + '_'+ I.COLUMN_NAMES
+ ''', ''index'';'
SELECT I.object_id, I.index_id, I.name,I.is_primary_key, I.type, I.is_unique, LEFT(I.COLUMN_NAMES,LEN(I.COLUMN_NAMES)-1) AS COLUMN_NAMES
SELECT I.object_id, I.index_id, I.name,I.is_primary_key, I.type, I.is_unique,
( SELECT C0.name +'_' AS [text()]
FROM sys.index_columns IC0
JOIN sys.columns C0 ON C0.object_id = IC0.object_id AND C0.column_id = IC0.column_id
WHERE IC0.index_id=I.index_id AND IC0.object_id = I.object_id
ORDER BY IC0.index_column_id
FOR XML PATH ('')
) AS COLUMN_NAMES
FROM sys.indexes I
AND objectproperty(I.object_id,'IsMSShipped ')=0
AND I.type NOT IN (0,3) -- Exclude XML indexes and heaps
WHERE I.name <> CASE WHEN I.is_unique=1 THEN 'AK_' ELSE 'IX_' END + object_name(I.object_id) + '_'+ I.COLUMN_NAMES
ORDER BY 1
Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.
SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.
There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.