November 15, 2005 at 4:33 am
I need to do a change in our database for our next release.
The problem consist of changing a primary key from int to bigint, without loosing the index (in fact that it makes last +1 after the change and not beginning from 0 or something else as this index contains disrupted numbering).
Here is the design of the table:
CREATE TABLE [Conf_data] (
[Data_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Collection_ID] [int] NOT NULL ,
[Father_ID] [int] NOT NULL ,
[Table_number] [int] NOT NULL ,
[Line_number] [int] NOT NULL ,
[Heading_ID] [int] NOT NULL ,
[Field01] [varchar] (2048) NULL ,
[Field02] [varchar] (2048) NULL ,
[Field03] [varchar] (2048) NULL ,
[Field04] [varchar] (2048) NULL ,
[Field05] [varchar] (2048) NULL ,
[Field06] [varchar] (2048) NULL ,
[Field07] [varchar] (2048) NULL ,
[Field08] [varchar] (2048) NULL ,
[Field09] [varchar] (2048) NULL ,
[Field10] [varchar] (2048) NULL ,
[Field11] [varchar] (2048) NULL ,
[Field12] [varchar] (2048) NULL ,
[Field13] [varchar] (2048) NULL ,
[Field14] [varchar] (2048) NULL ,
[Field15] [varchar] (2048) NULL ,
[Field16] [varchar] (2048) NULL ,
[Field17] [varchar] (2048) NULL ,
[Field18] [varchar] (2048) NULL ,
[Field19] [varchar] (2048) NULL ,
[Field20] [varchar] (2048) NULL ,
[Field21] [varchar] (2048) NULL ,
[Field22] [varchar] (2048) NULL ,
[Field23] [varchar] (2048) NULL ,
[Field24] [varchar] (2048) NULL ,
[Field25] [varchar] (2048) NULL ,
[Field26] [varchar] (2048) NULL ,
[Field27] [varchar] (2048) NULL ,
[Field28] [varchar] (2048) NULL ,
[Field29] [varchar] (2048) NULL ,
[Field30] [varchar] (2048) NULL ,
CONSTRAINT [PK_Conf_data] PRIMARY KEY NONCLUSTERED
(
[Data_ID]
) ON [PRIMARY]
) ON [PRIMARY]
I would like to use this script to do this change:
alter table conf_data drop constraint pk_conf_data
go
alter table conf_data alter column data_id bigint
go
alter table conf_data add constraint pk_conf_data primary key nonclustered (data_id)
Go
Doing the same with enterprise manager (design table) and doing a trace in the profiler, sql returns these lines:
CREATE TABLE esmguser.Tmp_Conf_data
(
Data_ID bigint NOT NULL,
Collection_ID int NOT NULL,
Father_ID int NOT NULL,
Table_number int NOT NULL,
Line_number int NOT NULL,
Heading_ID int NOT NULL,
Field01 varchar(2048) NULL,
Field02 varchar(2048) NULL,
Field03 varchar(2048) NULL,
Field04 varchar(2048) NULL,
Field05 varchar(2048) NULL,
Field06 varchar(2048) NULL,
Field07 varchar(2048) NULL,
Field08 varchar(2048) NULL,
Field09 varchar(2048) NULL,
Field10 varchar(2048) NULL,
Field11 varchar(2048) NULL,
Field12 varchar(2048) NULL,
Field13 varchar(2048) NULL,
Field14 varchar(2048) NULL,
Field15 varchar(2048) NULL,
Field16 varchar(2048) NULL,
Field17 varchar(2048) NULL,
Field18 varchar(2048) NULL,
Field19 varchar(2048) NULL,
Field20 varchar(2048) NULL,
Field21 varchar(2048) NULL,
Field22 varchar(2048) NULL,
Field23 varchar(2048) NULL,
Field24 varchar(2048) NULL,
Field25 varchar(2048) NULL,
Field26 varchar(2048) NULL,
Field27 varchar(2048) NULL,
Field28 varchar(2048) NULL,
Field29 varchar(2048) NULL,
Field30 varchar(2048) NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM esmguser.Conf_data)
EXEC('INSERT INTO esmguser.Tmp_Conf_data (Data_ID, Collection_ID, Father_ID, Table_number, Line_number, Heading_ID, Field01, Field02, Field03, Field04, Field05, Field06, Field07, Field08, Field09, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29, Field30)
SELECT CONVERT(bigint, Data_ID), Collection_ID, Father_ID, Table_number, Line_number, Heading_ID, Field01, Field02, Field03, Field04, Field05, Field06, Field07, Field08, Field09, Field10, Field11, Field12, Field13, Field14, Field15, Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23, Field24, Field25, Field26, Field27, Field28, Field29, Field30 FROM esmguser.Conf_data (HOLDLOCK TABLOCKX)')
DROP TABLE dbo.Conf_data
set @P1=0
declare @P2 int
set @P2=16388
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'EXECUTE sp_rename N''dbo.Tmp_Conf_data'', N''Conf_data'', ''OBJECT''
', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
ALTER TABLE dbo.Conf_data ADD CONSTRAINT
pk_conf_data PRIMARY KEY NONCLUSTERED
(
Data_ID
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX Conf_Data_Collection_Heading ON dbo.Conf_data
(
Collection_ID,
Heading_ID
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX Conf_Data_Collection_Table_Number ON dbo.Conf_data
(
Collection_ID,
Table_number
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TRIGGER Conf_data_Insert ON dbo.Conf_data
FOR INSERT
AS
SELECT Inserted.Data_ID from Inserted
So in fact it create an empty temp table with the new format, populate it with data from the "old" one and recreate the index.
As this table is enormous and take 95% of the database, I would know if:
My script can work without problem instead?
If not, is there something more faster than the script generated by sql profiler as it will take light years?
How to know what is the last index used to be sure that after the modif it does not "renumber" the index key but really starts at what we need?
Thanks by advance,
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
November 18, 2005 at 8:00 am
This was removed by the editor as SPAM
November 18, 2005 at 8:29 am
Enterprise Manager is not the best place to make this type of change. You've proved this by capturing the actions that EM performs on your table simply by changing the datatype on a column; EM rebuilds the table.
You could change your approach and use Query Analyzer to make the same change. You would still need to drop and readd the primary key constraint and nonclustered index on your column, but rebuilding the table would be unneccessary and from the sounds of it, it is the rebuild that is causing your indexed column values to not be what you expect? Maybe I am missing something here?
Do you have a test system? Run your script using Query Analyzer, use Profiler if you wish to compare the difference, but I do not think it will be necessary. This sounds like a large table so keep in mind that dropping and readding an index may still take a fair amount of time. Just a question, is there a reason why this PK was not set up as a clustered index?
November 21, 2005 at 3:38 am
Unless I'm mistaken changing a column data type requires a table rebuild and the various T SQL or gui commands only do this behind the scenes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply