How long will finish new column add to the biggest table?

Hi All,
Does anyone know, I will be add two new columns with small int data type to
existing table that has 121 million records. How long will finish? We use SQL
Server 2005 EE with SP2 (OS Windows 2003 with SP1).
Thanks!
-ChenThis is roughly impossible to predict. Make sure you add it using ALTER
TABLE and not through any designer in SSMS, and it will probably be fastest
if you make it NULL and don't put any NOT NULL / DEFAULT or other
constraints on it until after the column has been added.
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:41B2BF44-929A-481F-A06F-7DDC5B7992EF@.microsoft.com...
> Hi All,
> Does anyone know, I will be add two new columns with small int data type
> to
> existing table that has 121 million records. How long will finish? We use
> SQL
> Server 2005 EE with SP2 (OS Windows 2003 with SP1).
> Thanks!
> -Chen

How is 'change' defined ?

Can anyone tell me how a change is defined when replicating ? in particular, if an update is done to an existing row, but the value is actually the same as it was originally, does this count as a change and will the replication attempt to update/resolve
it or not ?
I am working in a scenario where it is quite important to minimise the time/quantity of replication so want to minimise any unnecessary replicating. Are there any techniques I can use to help with this ?
any change is replicated.
|||Bother!
But thanks
|||Sarah wrote:
> Can anyone tell me how a change is defined when replicating ? in particular, if an update is done to an existing row, but the value is actually the same as it was originally, does this count as a change and will the replication attempt to update/resolv
e it or not ?
> I am working in a scenario where it is quite important to minimise the time/quantity of replication so want to minimise any unnecessary replicating. Are there any techniques I can use to help with this ?
Basically the log is replicated. So any action that will make a commited
change in the log will be replicated.
Furthermore, if you are doing updated, there is a possiblity that
inserts/deletes are replicated instead.
Typically, if you have a table with a unique index and you make an
update which update this index, sql server will replace your update by
an insert/delete and this is what will be sent to subscribers
|||Hi Sarah,
If you have a lot of updates that are setting columns to the same value
repeatedly, you can minimize the the replication volume by adding additional
"where" criteria to your update statements. For example, if you have a
statement (psuedo-code)
set @.val1 = 'XXX'
set @.tblKey = '123'
update TABLEX set fieldA = @.val1 where tblKey= @.tblKey
and you execute this 100 times, it will generate 100 updates, and 100
replication transactions.
If you add a where clause like so
update TABLEX set fieldA = @.val1 where tblKey= @.tblKey and fieldA <> @.val1
ii will not update the row after the first time. This type of clause
generally doesn't add much overhead to the transaction.
Ed
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:A8B78B80-4AF0-43FE-B3A8-8678C072C3E2@.microsoft.com...
> Can anyone tell me how a change is defined when replicating ? in
particular, if an update is done to an existing row, but the value is
actually the same as it was originally, does this count as a change and
will the replication attempt to update/resolve it or not ?
> I am working in a scenario where it is quite important to minimise the
time/quantity of replication so want to minimise any unnecessary
replicating. Are there any techniques I can use to help with this ?