Re: Alter Table for 39 million rows

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/22/04 

Date: Tue, 21 Dec 2004 19:58:54 -0500

Adam Machanic wrote:
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> news:OxfYzl65EHA.3708@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.
>
> Thanks!
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic

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

Relevant Pages

  • Re: What Hardware?
    ... OK, I'll leave you to do the sums, do you know what the database growth ... You should plan your hardware for growth three years down ... > have this information you will be able to plan your disk, RAM, and CPU ... >>SQL Server 2000 Operations Guide: ...
    (microsoft.public.sqlserver.server)
  • Re: Alter Table for 39 million rows
    ... > data size, I guess the server doesn't have to shrink the data, but I ... >> NO TABLE GROWTH ... >> - Moving from smalldatetime to datetime caused growth and was slow as ...
    (microsoft.public.sqlserver.programming)
  • Re: Alter Table for 39 million rows
    ... Totally in-line with what I saw; adding a new nullable column shouldn't grow ... > NO TABLE GROWTH ... > - Adding a new varchar() column to the table seems to make no difference ... > - Moving from smalldatetime to datetime caused growth and was slow as ...
    (microsoft.public.sqlserver.programming)
  • Re: tempdb size
    ... SQL Server will not log the growth of any database in system tables. ... provide a higher Tempdb MDF and LDF size; so as auto growth will not happen. ... i want to put tempdb on some realy fast device, ...
    (microsoft.public.sqlserver.setup)
  • Re: Deadly spider in Whole Foods
    ... One of my wife's friends is moving to the big island to get away from the insanity. ... speaking humans will move into areas that support development and growth but there will always be spots on this world where one can get away from an excess of other humans. ...
    (rec.food.cooking)