> I have alarge table with 60 Million rows. I needed to add a new INT
column
> to the table and then assign it a value of 0. The Alter statement to add
> the column ran pretty quick:
> ALTER TABLE tb1
> ADD c1 INT NOT NULL
>
> but the update statement:
> UPDATE tb1
> SET c1 = 0
> ran for over 7 hours.

"TJTODD" <Thxomasx.Toddy@Siemensx.com> wrote in message
news:%23jH8epd7DHA.2480@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have alarge table with 60 Million rows. I needed to add a new INT
column
> to the table and then assign it a value of 0. The Alter statement to add
> the column ran pretty quick:
> ALTER TABLE tb1
> ADD c1 INT NOT NULL
>
> but the update statement:
> UPDATE tb1
> SET c1 = 0
> ran for over 7 hours.
>
> Is there a better way to do this?
>
> Thanks in advance
>
>

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:#sjJ2Dg7DHA.1040@TK2MSFTNGP10.phx.gbl...
> > I have alarge table with 60 Million rows. I needed to add a new INT
> column
> > to the table and then assign it a value of 0. The Alter statement to
add
> > the column ran pretty quick:
> > ALTER TABLE tb1
> > ADD c1 INT NOT NULL
> >
> > but the update statement:
> > UPDATE tb1
> > SET c1 = 0
> > ran for over 7 hours.
>
> Hmm. I wouldn't expect SQL Server to allow you to add a NOT NULL column
to
> a non-empty table unless you also specify a default constraint.
>
> For large tables, you may find it faster to create a new table using
SELECT
> INTO and then rename. This will be minimally logged in the SIMPLE or
> BULK_LOGGED recovery model. Example script below.
>
> CREATE TABLE tbl
> (
> c0 int not null
> )
>
> INSERT INTO tbl VALUES(1)
>
> SELECT
> c0,
> ISNULL(CAST(0 AS int), 0) AS c1
> INTO tbl_new
> FROM tbl
>
> DROP TABLE tbl
>
> EXEC sp_rename 'tbl_new', 'tbl'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "TJTODD" <Thxomasx.Toddy@Siemensx.com> wrote in message
> news:%23jH8epd7DHA.2480@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have alarge table with 60 Million rows. I needed to add a new INT
> column
> > to the table and then assign it a value of 0. The Alter statement to
add
> > the column ran pretty quick:
> > ALTER TABLE tb1
> > ADD c1 INT NOT NULL
> >
> > but the update statement:
> > UPDATE tb1
> > SET c1 = 0
> > ran for over 7 hours.
> >
> > Is there a better way to do this?
> >
> > Thanks in advance
> >
> >
>
>

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:%23sjJ2Dg7DHA.1040@TK2MSFTNGP10.phx.gbl...
> > I have alarge table with 60 Million rows. I needed to add a new INT
> column
> > to the table and then assign it a value of 0. The Alter statement to
add
> > the column ran pretty quick:
> > ALTER TABLE tb1
> > ADD c1 INT NOT NULL
> >
> > but the update statement:
> > UPDATE tb1
> > SET c1 = 0
> > ran for over 7 hours.
>
> Hmm. I wouldn't expect SQL Server to allow you to add a NOT NULL column
to
> a non-empty table unless you also specify a default constraint.
>
> For large tables, you may find it faster to create a new table using
SELECT
> INTO and then rename. This will be minimally logged in the SIMPLE or
> BULK_LOGGED recovery model. Example script below.
>
> CREATE TABLE tbl
> (
> c0 int not null
> )
>
> INSERT INTO tbl VALUES(1)
>
> SELECT
> c0,
> ISNULL(CAST(0 AS int), 0) AS c1
> INTO tbl_new
> FROM tbl
>
> DROP TABLE tbl
>
> EXEC sp_rename 'tbl_new', 'tbl'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "TJTODD" <Thxomasx.Toddy@Siemensx.com> wrote in message
> news:%23jH8epd7DHA.2480@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have alarge table with 60 Million rows. I needed to add a new INT
> column
> > to the table and then assign it a value of 0. The Alter statement to
add
> > the column ran pretty quick:
> > ALTER TABLE tb1
> > ADD c1 INT NOT NULL
> >
> > but the update statement:
> > UPDATE tb1
> > SET c1 = 0
> > ran for over 7 hours.
> >
> > Is there a better way to do this?
> >
> > Thanks in advance
> >
> >
>
>