Changing NULL, to NOT NULL

SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.You drop statistics in the same way that you drop indexes. Typically, you
don't have both statistics and indexes on the same column - except, say, if
you have an index on a single column and statistics on a pair of columns,
and vice-versa. In your case, it looks like the Database Tuning Advisor
recommended the stats and the DBA running the DTA allowed them to be built.
You'll have to run:
DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.|||Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say, if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Well, that's the issue. If you have a lot of data, it will take a long
time. Your log may fill up during that time. Ultimately, you may need to
create a new table, with the intended DDL. Then, you move the data in
chunks to the other table. Meanwhile, you create a partitioned view (with
the same name as the old table) that includes both tables. However, you
will need to add INSTEAD OF triggers for any INSERT/UPDATE/DELETE activity
that will occur during that time. Once all data have been moved, you can
drop the original table and view, renaming the new table.
The attached code has an example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:3E756320-3BA2-46CA-A2E6-9DBD404D83B4@.microsoft.com...
Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large
table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say,
> if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be
> built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects
> access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
name="Alter.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Alter.sql"
-- Listing 1
create table dbo.t1
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
)
create clustered index C_t1 on dbo.t1 (dt)
create table dbo.t2
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
, other1 bit not null
default (0)
, other2 varchar (30) null
)
create clustered index C_t2 on dbo.t2 (dt)
create view dbo.t
as
select
id
, dt
, junk
from
dbo.t2
union all
select
id
, dt
, junk
from
dbo.t1
go
-- Listing 2
create table dbo.threshold
(
threshold datetime not null
)
insert dbo.threshold values ('1995-01-01')
go
-- Listing 3
create trigger tri_t on dbo.t instead of insert
as
insert dbo.t1 (id, dt, junk)
select id, dt, junk
from inserted
where dt >= (select threshold from dbo.threshold)
insert dbo.t2 (id, dt, junk)
select id, dt, junk
from inserted
where dt < (select threshold from dbo.threshold)
go
-- Listing 4
create trigger tru_t on dbo.t instead of update
as
if (update (dt))
begin
-- no crossing of threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt < z.threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt >= z.threshold
-- threshold crossed
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
insert dbo.t1 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
insert dbo.t2 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
end
else
begin
update x
set
junk = i.junk
from
inserted i
join dbo.t1 x on x.id = i.id
where
i.dt >= (select threshold from dbo.threshold)
update x
set
junk = i.junk
from
inserted i
join dbo.t2 x on x.id = i.id
where
i.dt < (select threshold from dbo.threshold)
end
go
-- Listing 5
create trigger tru_t on dbo.t instead of delete
as
if @.@.ROWCOUNT = 0
return
delete x
from
deleted d
join dbo.t1 x on x.id = d.id
delete x
from
deleted d
join dbo.t2 x on x.id = d.id
go
-- populate the table
declare
@.year smallint
, @.month tinyint
, @.day tinyint
, @.id int
, @.date datetime
, @.icount int
, @.max int
select
@.year = 1995
, @.id = 1
while @.year < 2004
begin
set @.month = 1
while @.month <= 12
begin
set @.day = 1
while @.day <= case when @.month in (1, 3, 5, 7, 8, 10, 12) then 31
when @.month in (4, 6, 9, 11) then 30
when @.year % 4 = 0 then 29
else 28 end
begin
set @.date = cast (@.year as char (4))
+ replace (str (@.month, 2), ' ', '0')
+ replace (str (@.day, 2), ' ', '0')
select
@.max = rand () * 1000
, @.icount = 1
while @.icount <= @.max
begin
insert t1 (id, dt, junk)
values (@.id, @.date, replicate ('X', 20))
select
@.icount = @.icount + 1
, @.id = @.id + 1
end
set @.day = @.day + 1
end
set @.month = @.month + 1
end
set @.year = @.year + 1
end
go
-- Listing 6
-- migrate the data
declare
@.date datetime
, @.str varchar (8000)
select
@.date = min (dt)
from
t1
while @.date is not null and not datepart (hh, getdate()) between 8 and 18
begin
begin tran
update threshold
set
threshold = dateadd (dd, 1, @.date)
insert t2 (id, dt, junk)
select id, dt, junk
from t1
where dt = @.date
delete t1
where dt = @.date
commit tran
select
@.date = min (dt)
from
t1
end
/*
drop view t
drop table t1
drop table t2
drop table threshold
*/
--=_NextPart_000_0A6F_01C77871.851AEB80--|||> After having a further conversation, it seems they have tried the alter
> already and it ran for several hours before timing out ( a very large
> table).
> This implies to me that it is doing a table rebuild, even though all data
> in
> that column is already NOT NULL and only the DDL needs to be changed.
> Am I probably right here?
That depends, did they open Enterprise Manager and click around in the GUI
to make the change? By default, in most cases, when you click OK Enterprise
Manager will do exactly that behind your back: create a new table, move the
data over, drop the old table, rename the new table to the old name, etc.
No exactly pretty.
You should probably wait to attempt this during a maintenance window (e.g.
on a Sunday at midnight), and even enforce it with setting the database to
SINGLE_USER, and then you won't have to worry about blocking users, or being
blocked by users, or being slowed down by other activity in the database.
If it still takes too long, you may have to use Tom's solution, which is a
bit more complex but is certainly better than what Enterprise Manager tries
to do.|||> You should probably wait to attempt this
And by "this" I mean an ALTER statement in Query Analyzer, not clicking OK
to the change in Enterprise Manager.|||Yes, they tried to do it through the GUI.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>|||At present, I'm on a dev server (our only 2005 box).
As it happens, the ALTER statement does see that it's a simple DDL change
and took a couple of seconds.
I'm releived that SQL Server is smart enough to do that.
Thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>