update or rename column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update or rename column?

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-10-16 : 13:22:44

We've added a new bit field to a table that has about 6M records, set it to deafult (0), but forgot to set it to not null. Now, the field has value NULL for all records when we wanted it to be 0. We tried altering or dropping column but did not work. We got constraint error. We thought of just updating the value from null to 0 but it would take hours to do it. We also thought of renaming the field and then just create the correct field.

What's the best way to get the correct field added to this table? Pls help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-16 : 14:03:22

Drop the default constraint first, then you can drop the column. The error message should tell you the name of the constraint, paste it into:

ALTER TABLE myTable DROP CONSTRAINT constraintName
ALTER TABLE myTable DROP COLUMN myBitColumn

Kristen
Test

22859 Posts

Posted - 2004-10-17 : 01:15:37

"but it would take hours to do it"

It will take just as long to update the database when you use ALTER to add the column with a DEFAULT constraint AND set it to NOT NULL. If it really is that slow (I'm sceptical for 6M rows, but.) you might be better off having it as NULLable if you don't have downtime available ... and enforce NOT NULL using a trigger etc.

Then perhaps set it to NOT NULL during the next scheduled downtime

You can set all existing rows to (0), in a non intrusive way, using something like


DECLARE @intRowCount int
SET @intRowCount = 1
WHILE @intRowCount > 0
BEGIN
  SET ROWCOUNT 10000 -- Number of rows to update per batch
  UPDATE MyTable SET MyColumn = 0 WHERE MyColumn IS NULL
  SELECT @intRowCount = @@ROWCOUNT
  SET ROWCOUNT 0
  WAITFOR DELAY '00:00:05' -- 5 Second delay
END

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-17 : 10:27:56

That will probably get progressively slower as it will table scan - if you want to just change it to 0 it will be better to loop through an indexed field to make the changes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-17 : 13:59:06

Good point ... but trying to write an example it doesn't appear to be THAT easy :-( Dunno if this is any good?


DECLARE @intRowCount int,
        @minPK varchar(10), -- Needs to be type of PK column
        @maxPK varchar(10)
SELECT	@intRowCount = 1,
	@minPK = '' -- Minimum value for data type
WHILE @intRowCount > 0
BEGIN
  SELECT TOP 10000	-- Change to apporpriate batch size
  	@maxPK = MyPKColumn
  FROM	MyTable
  WHERE     MyPKColumn >= @minPK
        AND MyColumn IS NULL
  ORDER BY MyPKColumn

  UPDATE MyTable
  SET	MyColumn = 0
  WHERE	    MyPKColumn BETWEEN @minPK AND @maxPK
	AND MyColumn IS NULL
  SELECT @intRowCount = @@ROWCOUNT
  SELECT @minPK = @maxPK
  WAITFOR DELAY '00:00:05' -- 5 Second delay
END

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-17 : 18:42:18

You can't do it without knowing the table structure. Will also need to know the number of nulls and distribution.
It may be best to do a single table scan to get all the PK's or a unique column into a temp table then loop through that for the update or to use a non-unique indexed column.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-18 : 00:28:21

As its a new column every ow in the database has got to be set (unless that row has, in the meantime, been set by some other source)

Can you force the sequence that an UPDATE works in? If so I suppose it would be possible to use SET ROWCOUNT 10000 and some variable that remembered the highest PK value that had been reached - and then resume from that point on the next iteration

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 03:42:44

How do you find the 10000th PK?
You would get the next 10000 PKs into a temp table, update with a join then use the temp table to get the next batch.
That will always work but is not really worth it if there is an index with a good distribution that you can loop through.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-18 : 14:29:34

I was thinking of something like:


SELECT @MaxPK = '',     -- Minimum initial value
       @intRowCount = 1 -- Force initial iteration
WHILE @intRowCount > 0
BEGIN
  SET ROWCOUNT 10000
  UPDATE U
  SET  MyColumn = 0,
       @MaxPK = CASE WHEN @MaxPK < MyPK THEN @MaxPK = MyPK ELSE @MaxPK END
  FROM MyTable
  WHERE MyPK > @MaxPK
  ORDER BY MyPK  -- If I could force updating in PK order ...
  SELECT @intRowCount = @@ROWCOUNT
  SET ROWCOUNT 0
END

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 08:43:40

>> -- If I could force updating in PK order
You can't. You can do it using derived tables probably but simpler to use a separate table to do the same thing as I suggested before.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 14:17:12

OK, thanks. That was my [implied] question - whether it was worth trying to bludgeon an implied ORDER BY in an update, or use some peripheral "todo-list" type table.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources