Alter Table for 39 million rows
Jorge
I'm trying to alter a table column from tinyint to a smallint, however, the
table contains > 39 million rows and running the alter table takes a long
time. Is there any other way to do the same faster.
I was thinking about creating another column with the new datatype and then
dropping the oldcolumn and renaming the new column to the old column's name.
Alejandro Mesa
EM creates a new table with the new layout and then move the data.
Better if you try from Query Analyzer:
alter table dbo.t alter column mycolumn smallint
AMB
Adam Machanic
shifted around to make room for the new data...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jorge" <Jo...@discussions.microsoft.com> wrote in message
news:3909DAAD-9778-4D3E...@microsoft.com...
Kalen Delaney
a datatype to something larger should only be a metadata only change, and
the physical change in the rows should not happen until the rows are updated
after the alter.
If you have indexes on the columns, it does actually have to update the data
in some cases. If you are doing this in EM, as Alejandro suggested, first
try using just ALTER TABLE. If that still seems to take long, then try
dropping any indexes before the ALTER.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%236gd8j5...@TK2MSFTNGP10.phx.gbl...
Adam Machanic
one byte? Wouldn't every row be grown by one byte to compensate?
This example certainly makes it seem that way (notice that the data size
grows from 73 MB to 149 MB):
use tempdb
go
create table blah(somenum tinyint)
go
insert blah
select 1
from pubs..authors a,
pubs..authors b,
pubs..authors c,
pubs..authors d,
pubs..authors e
go
sp_spaceused 'blah'
go
-- blah 6436343 73600 KB 73560 KB 8 KB 32 KB
alter table blah
alter column somenum smallint
go
sp_spaceused 'blah'
go
-- blah 6436343 149168 KB 149104 KB 8 KB 56 KB
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:%23k2PIC6...@TK2MSFTNGP11.phx.gbl...
Kalen Delaney
will fit into a tinyint, in many cases SQL Server will not actually increase
the size of the column until necessary. It will update the metadata in
syscolumns, but it won't go through all 70 gagillion rows and add an extra
two bytes. It will wait until it has to do something else to that row before
changing the physical storage.
I have done a lot of testing on this, but I haven't tested every single
possible scenario. Sometimes SQL Server will change all the rows during the
ALTER TABLE, and sometimes it won't.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%23rD4gR6...@TK2MSFTNGP12.phx.gbl...
Adam Machanic
>
> I have done a lot of testing on this, but I haven't tested every single
> possible scenario. Sometimes SQL Server will change all the rows during
the
> ALTER TABLE, and sometimes it won't.
Can you show me one where it won't? I just tested a few, and it seemed
to change the row size during the ALTER every single time.
Thanks!
Mark Wilden
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> news:OxfYzl65...@TK2MSFTNGP14.phx.gbl...
> >
> > I have done a lot of testing on this, but I haven't tested every single
> > possible scenario. Sometimes SQL Server will change all the rows during
> the
> > ALTER TABLE, and sometimes it won't.
>
> Can you show me one where it won't? I just tested a few, and it
seemed
> to change the row size during the ALTER every single time.
It sure seems that SQL Server would have to change the data in every row, if
the metadata says that the data that follows the changed column is 2 bytes
away from the column that precedes it, when it used to be just 1 byte.
David Gugick
I did a bunch of tests as well:
NO TABLE GROWTH
- Adding a new varchar() column to the table seems to make no difference
and ran fast
- Changing a varchar(20) to a varchar(40) didn't make a difference and
ran fast
- Changing from datetime to smalldatetime made no difference and ran
fast
- Changing from a char(20) to a char(10) made no difference and ran fast
as long as the data won't get truncated
TABLE GROWTH
- Moving from a fixed character type char(20) to char(40) forced the
table to grow
- Moving from a char(20) to a varchar(40) forced the table to grow
- Moving from a varchar(20) to a char(20) caused growth and was slow (in
the test I did).
- Moving from smalldatetime to datetime caused growth and was slow as
well. Even using NULLs for all values in the datetime test caused
growth.
- Changing from INT to BIGINT caused the table to grow
--
David Gugick
Imceda Software
www.imceda.com
Mark Wilden
news:u%23szgF85...@TK2MSFTNGP11.phx.gbl...
> - Changing from datetime to smalldatetime made no difference and ran
> fast
So I guess SQL Server doesn't snug up the columns until it needs to.
There'll be a gap after the column in such a case.
Adam Machanic
the data -- it's just meta-data. Try making that varchar() non-null with a
default, though. That should increase the size. And dropping a column's
data size, I guess the server doesn't have to shrink the data, but I wonder
if it's de-allocated internally? I assume you can shrink the file after
downsizing the datatype.
"David Gugick" <davidg...@imceda.com> wrote in message
news:u%23szgF85...@TK2MSFTNGP11.phx.gbl...
>
Andrew J. Kelly
only doing the work when it has to and as such it can spread the load so to
speak.
--
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%23kLuwy8...@tk2msftngp13.phx.gbl...