alter column from int to bigint - timeframe

[Dave]

I’m going to alter the CommandId column from int to bigint in the
following table, and the table as about 1.7 billion rows. My DB is
set to recovery model of simple so that will help keep the transaction
log down, and the box is decently beefy with no other activity on it.

I was wondering if someone could give me some idea of how long the
operation may take? (I apologize for the generic nature of the
question.)

CREATE TABLE [dbo].[Commands](
[CommandId] [int] NOT NULL,
[OccuranceTime] [smalldatetime] NOT NULL,
[fkField1Id] [tinyint] NOT NULL,
[fkField2Id] [tinyint] NULL,
[fkField3Id] [int] NOT NULL,
[fkField4Id] [int] NULL,
[fkField5Id] [int] NULL,
[fkField6Id] [int] NULL,
[fkField7Id] [int] NULL
) ON [PRIMARY]

Note: the CommandId field was an PK / Identity field but I’m planning
on killing the PK prior to altering the column, so the alter column
will be allowed.

Thanks, dave

[Andrew J. Kelly]

There is absolutely no way to estimate the time without first hand knowledge
of your system and prior experience as there are so many factors that come
into play. Hardware alone can make a huge difference. What I can tell you is
that you may be better off exporting the data, truncating the table,
altering it and importing the data back in.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Dave" <djoha...@gmail.com> wrote in message
news:5c389a2a-b646-4c5a...@y10g2000prg.googlegroups.com...
> I�m going to alter the CommandId column from int to bigint in the

> following table, and the table as about 1.7 billion rows. My DB is
> set to recovery model of simple so that will help keep the transaction
> log down, and the box is decently beefy with no other activity on it.
>
> I was wondering if someone could give me some idea of how long the
> operation may take? (I apologize for the generic nature of the
> question.)
>
> CREATE TABLE [dbo].[Commands](
> [CommandId] [int] NOT NULL,
> [OccuranceTime] [smalldatetime] NOT NULL,
> [fkField1Id] [tinyint] NOT NULL,
> [fkField2Id] [tinyint] NULL,
> [fkField3Id] [int] NOT NULL,
> [fkField4Id] [int] NULL,
> [fkField5Id] [int] NULL,
> [fkField6Id] [int] NULL,
> [fkField7Id] [int] NULL
> ) ON [PRIMARY]
>

> Note: the CommandId field was an PK / Identity field but I�m planning

[Gert-Jan Strik]

I can't give you the answer how long it will take. What I can tell you
is what will happen. With that information, you should be able to build
a table on your system that is big enough to test and to extrapolate the
results for your big table.

For your situation (a Primary Key column that needs changing), it is a
three step process:
1. Remove all indexes. First remove all nonclustered indexes, then the
clustered index
2. Alter the column
3. Add all indexes. Start with the clustered index

In step 1, the removal of the nonclustered indexes is instantaneous. I
think for removing the clustered index the entire table is touched,
although I am not 100% sure about that.

In step 2, the alteration from int to bigint will be instanteneous. That
is, because this schema change is not propagated to the data pages until
this is necessary

In step 3, the creation of the clustered index will restructure the
entire table. So it will touch all data pages, and it will be a complete
reindex. The rows are divided over the (new) pages based on their new
size and the fillfactor. This can take considerable time, and a lot of
extra space (even if it is temporary). Next, recreating all nonclustered
indexes will take time, there is nothing special about that.

--
Gert-Jan
SQL Server MVP

[Erland Sommarskog]

Gert-Jan Strik (sorrytoomuc...@xs4all.nl) writes:
> In step 2, the alteration from int to bigint will be instanteneous. That
> is, because this schema change is not propagated to the data pages until
> this is necessary

I don't think so. Look at this:

CREATE TABLE test (a int NOT NULL,
b varchar(23) NOT NULL DEFAULT '')

INSERT test (a)
SELECT object_id + column_id FROM sys.columns

go
EXEC sp_spaceused test
PRINT 'alter column a'
set statistics io on
alter table test alter column a bigint NOT NULL
set statistics io off
EXEC sp_spaceused test
PRINT 'alter column b'
set statistics io on
alter table test alter column b varchar(30) NOT NULL
set statistics io off
EXEC sp_spaceused test
go
drop table test

The table grows in size directly, and IO are generated, which does
not happen for the second change.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Dave (djoha...@gmail.com) writes:
> I�m going to alter the CommandId column from int to bigint in the

> following table, and the table as about 1.7 billion rows. My DB is
> set to recovery model of simple so that will help keep the transaction
> log down, and the box is decently beefy with no other activity on it.
>
> I was wondering if someone could give me some idea of how long the
> operation may take? (I apologize for the generic nature of the
> question.)

In difference to Andy and Gert-Jan I am prepared to give a straight
answer: a looooong time.

The best strategy is probably to add a new column bigint column, and
make it nullable. Then populate this column in batches of, hm, 100000
rows. This will keep your transaction log in check. However, if CommandId
is an IDENTITY column, this will not work out well, because you cannot
make it an IDENTITY column after the fact. In that case, you are best
off making a copy of the table, as Andy suggested, and move data over,
again in batching.

If you use ALTER TABLE, your log will explode as it will be all one
transaction.

Whatever, you need to test this on a copy of the database before you
go ahead. A change like this one requires very careful planning.

[Uri Dimant]

Dave
Also some experience
http://dimantdatabasesolutions.blogspot.com/2009/12/think-before-you-use-alter-table.html

"Dave" <djoha...@gmail.com> wrote in message
news:5c389a2a-b646-4c5a...@y10g2000prg.googlegroups.com...

[Dan Guzman]

> Note: the CommandId field was an PK / Identity field but I�m planning

> on killing the PK prior to altering the column, so the alter column
> will be allowed.

Is the primary key clustered? In that case, the entire table will be
rebuilt when you remove the primary key (along with any non-clustered
indexes). Also, as mentioned in this thread, the alteration of the
fixed-length datatype will require all data pages to be changed during the
ALTER will be fully-logged. Each page will be changed 3 times (drop
clustered index, alter table, create clustered).

You might consider creating a new table and load with the clustered index in
place. This will be minimally-logged with a table lock hint. Afterward,
drop the old table, rename the table and create non-clustered indexes and
constraints. Same script below. I ran a cursory test on my PC and 100M
rows took 16 minutes but YMMV. Keep in mind that you'll need space for both
the old and new table concurrently and perhaps extra tempdb space as well.

CREATE TABLE [dbo].[Commands_New](
[CommandId] [bigint] NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Commands_New PRIMARY KEY CLUSTERED,

[OccuranceTime] [smalldatetime] NOT NULL,
[fkField1Id] [tinyint] NOT NULL,
[fkField2Id] [tinyint] NULL,
[fkField3Id] [int] NOT NULL,
[fkField4Id] [int] NULL,
[fkField5Id] [int] NULL,
[fkField6Id] [int] NULL,

[fkField7Id] [int] NULL);
GO

SET IDENTITY_INSERT [dbo].[Commands_New] ON;
GO

INSERT INTO dbo.Commands_New WITH (TABLOCKX) (
CommandId,
OccuranceTime,
fkField1Id,
fkField2Id,
fkField3Id,
fkField4Id,
fkField5Id,
fkField6Id,
fkField7Id)
SELECT
CommandId,
OccuranceTime,
fkField1Id,
fkField2Id,
fkField3Id,
fkField4Id,
fkField5Id,
fkField6Id,
fkField7Id
FROM Commands;
GO

SET IDENTITY_INSERT [dbo].[Commands_New] OFF;
GO

DROP TABLE dbo.Commands;
GO

EXEC sp_rename 'dbo.Commands_New','Commands';
EXEC sp_rename 'dbo.PK_Commands_New','PK_Commands';
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Dave" <djoha...@gmail.com> wrote in message
news:5c389a2a-b646-4c5a...@y10g2000prg.googlegroups.com...

> I�m going to alter the CommandId column from int to bigint in the

> following table, and the table as about 1.7 billion rows. My DB is
> set to recovery model of simple so that will help keep the transaction
> log down, and the box is decently beefy with no other activity on it.
>
> I was wondering if someone could give me some idea of how long the
> operation may take? (I apologize for the generic nature of the
> question.)
>
> CREATE TABLE [dbo].[Commands](
> [CommandId] [int] NOT NULL,
> [OccuranceTime] [smalldatetime] NOT NULL,
> [fkField1Id] [tinyint] NOT NULL,
> [fkField2Id] [tinyint] NULL,
> [fkField3Id] [int] NOT NULL,
> [fkField4Id] [int] NULL,
> [fkField5Id] [int] NULL,
> [fkField6Id] [int] NULL,
> [fkField7Id] [int] NULL
> ) ON [PRIMARY]
>

> Note: the CommandId field was an PK / Identity field but I�m planning

[Dave]

On Dec 19, 12:28 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Dave (djohanns...@gmail.com) writes:
> > I m going to alter the CommandId column from int to bigint in the

I was away due to travels so could not read these great responses
until now.

Given the CommandId column is a clustered index / PK and IDENTITY
column - I agree with making a copy of the table, and moving data over
in batches.

Can you suggest some optimal batching techniques?

I can think of using BCP, or SSIS or perhaps the ROWCOUNT property
somehow. I would like to to do it all in SQL Server and avoid the
likes of SSIS if possible.

thanks, dave

[Erland Sommarskog]

Dave (djoha...@gmail.com) writes:
> Given the CommandId column is a clustered index / PK and IDENTITY
> column - I agree with making a copy of the table, and moving data over
> in batches.
>
> Can you suggest some optimal batching techniques?

Intervals on the clustered index key:

INSERT (...)
SELECT ...
FROM tbl
WHERE id BETWEEN @start AND @start + @length - 1

A length of 500000 is probably reasonable, depends a little on how wide
rows are.

An alternative is put the database in BULK_LOGGED recovery, and load the
table with SELECT INTO or BCP. This will reduce logging considerably.
But SELECT INTO it's more difficult to get the table right, and BCP means
that you first need to unload to disk. In SQL 2008 you can get minimally
logged when INSERT into an empty under some cicrumstances.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx