Hello
I have been getting some random errors when building our data warehouse.
Generally, the sequence that we add a new field to the data warehouse is:
1. Add a new column
2. Build an index on the column
3. Set column to 'Yes' based on certain conditions
4. Set all values with NULL to 'No'
The specific table that I am dealing with has roughly 2.5 million records.
What happens is that the "step 4" listed above sets all values with NULL to No and quite randomly some "Yes" values back to "No".
I am certain that there is no fault in the code as various members of our development team have checked the code. I have added the code to the bottom of the note.
I have tried the code on 3 servers with different configurations and all have the same issue. The current server that I am looking at has SQL2000sp3.
Has anyone else experienced anything like this.
Incidentally, the problem appears to go away if I force option (Maxdop 1).
Adding/removing clustered indexes makes no difference.
One of our DBAs thinks that there may be a fault with the index collation - but I don't know.
Any advice would be welcomed.
In the example below, the second update should update 2172560 records.
We are able to re-write each statement where the problem exhibits itself - but it takes a long time to find, check and test and we can only spare two developers to recode.
Thanks
Mark Elvin
------------------------------------------
ALTER TABLE dbo.imp_tblPerson_1 Add [GeneralRetailContactFlag] varchar(3) NULL
go
create index I_tblPersonGeneralRetailContactFlag on dbo.imp_tblPerson_1 (GeneralRetailContactFlag)
go
update dbo.imp_tblPerson_1
set [GeneralRetailContactFlag] = 'No'
from dbo.imp_tblPerson_1 p
where p.Age > 0 and p.Age < 16
--(284374 row(s) affected)
select GeneralRetailContactFlag, count(*) from dbo.imp_tblPerson_1 group by GeneralRetailContactFlag
-- GeneralRetailContactFlag
-- ------------------------ -----------
-- NULL 2172560
-- No 284374
update dbo.imp_tblPerson_1 with (tablock)
set [GeneralRetailContactFlag] = 'Yes'
where [GeneralRetailContactFlag] is null
--(2350671 row(s) affected)
select GeneralRetailContactFlag, count(*) from dbo.imp_tblPerson_1 group by GeneralRetailContactFlag
-- GeneralRetailContactFlag
-- ------------------------ -----------
-- No 106263
-- Yes 2350671
I have been getting some random errors when building our data warehouse.
Generally, the sequence that we add a new field to the data warehouse is:
1. Add a new column
2. Build an index on the column
3. Set column to 'Yes' based on certain conditions
4. Set all values with NULL to 'No'
The specific table that I am dealing with has roughly 2.5 million records.
What happens is that the "step 4" listed above sets all values with NULL to No and quite randomly some "Yes" values back to "No".
I am certain that there is no fault in the code as various members of our development team have checked the code. I have added the code to the bottom of the note.
I have tried the code on 3 servers with different configurations and all have the same issue. The current server that I am looking at has SQL2000sp3.
Has anyone else experienced anything like this.
Incidentally, the problem appears to go away if I force option (Maxdop 1).
Adding/removing clustered indexes makes no difference.
One of our DBAs thinks that there may be a fault with the index collation - but I don't know.
Any advice would be welcomed.
In the example below, the second update should update 2172560 records.
We are able to re-write each statement where the problem exhibits itself - but it takes a long time to find, check and test and we can only spare two developers to recode.
Thanks
Mark Elvin
------------------------------------------
ALTER TABLE dbo.imp_tblPerson_1 Add [GeneralRetailContactFlag] varchar(3) NULL
go
create index I_tblPersonGeneralRetailContactFlag on dbo.imp_tblPerson_1 (GeneralRetailContactFlag)
go
update dbo.imp_tblPerson_1
set [GeneralRetailContactFlag] = 'No'
from dbo.imp_tblPerson_1 p
where p.Age > 0 and p.Age < 16
--(284374 row(s) affected)
select GeneralRetailContactFlag, count(*) from dbo.imp_tblPerson_1 group by GeneralRetailContactFlag
-- GeneralRetailContactFlag
-- ------------------------ -----------
-- NULL 2172560
-- No 284374
update dbo.imp_tblPerson_1 with (tablock)
set [GeneralRetailContactFlag] = 'Yes'
where [GeneralRetailContactFlag] is null
--(2350671 row(s) affected)
select GeneralRetailContactFlag, count(*) from dbo.imp_tblPerson_1 group by GeneralRetailContactFlag
-- GeneralRetailContactFlag
-- ------------------------ -----------
-- No 106263
-- Yes 2350671