Re: Adding columns to SQL Server tables dynamically
From: David Browne (meat_at_hotmail.com)
Date: 12/04/04
- Next message: Manny: "Connection String Machine Independent."
- Previous message: fahad: "ADO .NET and Excel ..."
- In reply to: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Next in thread: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Reply: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 4 Dec 2004 17:05:17 -0600
"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:%23WE4yqj2EHA.936@TK2MSFTNGP12.phx.gbl...
> David,
>
> So would you recommend a production app to add columns through ado.net
> sqlcommands? In other words; is it okay to have a production application
> add columns on the fly to the database, in addition to
> select/update/delete statements? I always thought that was not a
> recommended practise precisely because you'd have to run DBCC DBREINDEX on
> your production servers more frequently.
>
I wouldn't want to see columns added nearly as frequently as
select/update/delete. But for, say, daily import job it isn't a very big
deal, especially if the you only insert new rows with the added columns, not
update existing rows. New nullable columns are really free to add: it's
like they were there the whole time, but null on all existing rows. Mass
updates which significantly increase the size of the target rows will cause
page splits, and will take a long time.
It really depends on the frequency of the changes and the size of the table.
If the table has 100 million rows, then I would never ALTER it except over
the Christmas holiday. For any table under a million rows, it's just not a
big deal. And for any Sql Server whose active database is smaller than the
amount of ram dedicated to Sql Server, it's just not a big deal, since Sql
Server index fragmentation only really affects disk throughput.
"Back in the day", say 1995, RAM sizes were much smaller, and disk access
was relatively faster. Database servers used _much_ more physical IO than
they do these days. Any issue affecting physical IO throughput went right
to the bottom line of server performance.
For instance you might have 4 100 mhz Pentium processors, 128 megs of ram
accessed across a 33mhz bus, and 20gigs of storage spread among 10 hard
drives. The hard drives were almost half as fast as todays drives, and the
large number of spindles probably made up for it. But the RAM was 10x
slower and 10x smaller, and the CPU was 30x slower. In addition to log
flushing, any query of any size, most sorts, and many DML operations
required really significant physical IO.
Compare that with a low-end server today. 2 cpu's at 3.4ghz 2gigs of ram on
the other end of an 800mhz memory bus, and 2 35gig SCSI drives in a mirror.
It's not just that today's machine is faster, it's a different kind of
machine: it's a machine where you only touch the disks when you need to
push something to non-volatile storage. It's not that it's so fast that you
don't have performance problems: you just have different performance
priorities.
David
- Next message: Manny: "Connection String Machine Independent."
- Previous message: fahad: "ADO .NET and Excel ..."
- In reply to: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Next in thread: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Reply: Sahil Malik: "Re: Adding columns to SQL Server tables dynamically"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
