April 2, 2003 at 8:49 am
Hi, I need to cluster an existing primary key index on a table with 300,000 rows in database that is in almost constant use. Is it possible to do this without dropping the existing primary key? I need to do this with the minimum of impact on the end users
April 2, 2003 at 10:14 am
Assuming your table is not Clustered already, the operation to effect a CI is major because it will require a physical reordering of all data, and all existing indexes will have to be rebuilt. i.e. a table lock [yes it will block other activity big-time].
You have 2 alternatives
1. pick a time when it's quiet [say 11pm Saturday after visiting the pub to get your confidence up!] with no db activity
2. make a secondary table and stage data there [assuming space for second copy]
2.1 create table newtbl(a int, b varchar(255), constraint PK_newtbl primary key clustered (a))
2.2 create triggers on oldtbl (for insert, update, delete) so that any changes get fed through to newtbl
2.3 populate newtbl from oldtbl (where not exists in case trigger got there first)
2.4 watch and verify that newtbl is kosher
2.5 [within transaction] cut across by
begin tran
dropping all FK to oldtbl
drop oldtbl
sp_rename newtbl,oldtbl
create all FK to from oldtbl (NOCHECK)
commit
where again you can pick a quiet moment for the cut-across, and after due backups!
the advantage of 2.x is that the data has been copied across already so you have only a couple of quick metadata operations [so normal service is resumed quickly]
Dick
PS usual indemnity rules apply !
April 2, 2003 at 12:33 pm
It could be a no brainer. How many other indexes are there? How many keys? One I hope. No, you cannot do this with out dropping the primary key. Don't use the gooey when doing this - I've seen it hang once to many time - I would run the change on the server itself.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.AccountChangeLog
DROP CONSTRAINT PK_AccountChangeLog
GO
ALTER TABLE dbo.AccountChangeLog ADD CONSTRAINT
PK_AccountChangeLog PRIMARY KEY CLUSTERED
(
myid
) ON [PRIMARY]
GO
COMMIT
John Zacharkan
John Zacharkan
April 3, 2003 at 3:38 am
Thanks for the assistance
April 3, 2003 at 4:01 am
If you take a copy the table with the current indexes, you can practice on that table to a: get it right and b: see how long it takes. Depending on how wide the table is (how many columns) it might not take more than a couple of minutes to re-index.
Jeremy
April 4, 2003 at 5:07 pm
Why do you want to 'cluster an existing primary key index on a table'?
By default, primary key is a unique clustered index.
April 5, 2003 at 2:32 am
boz: In SQL 7 it's not the default. What if, by design, you chose that another clusterization suited your needs better when the table was created?
/Hans
Edited by - hanslindgren on 04/06/2003 5:08:10 PM
April 10, 2003 at 7:45 am
In case if you are using the method suggested by Mr. DickBaker, then you need to recompile all its dependent objects (stored procedures, triggers, if exists). Otherwise, the SQL will still remember the old object within the dependent object's query plan. I just thought of sharing the info.
April 10, 2003 at 8:08 am
Dharma is basically wrong! See extract from BOL below
IMHO it is very rare to [have/want to] run sp_recompile since this would happen next actual usage. This is just as well as it is a pain to iterate through all affected objects.
The exception is when you have finished all metadata changes and you want to precompile sprocs etc [to save this slight overhead on first usage], but is NOT a concrete requirement.
Dick
extract from BOL (MSSQL-2000) ..
Recompiling a Stored Procedure
As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft® SQL Server™ 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply