Unexpected Columns being set to NULL with record updates

Question

Unexpected Columns being set to NULL with record updates

d_s_man

SSC Rookie

Points: 47
More actions
September 9, 2010 at 8:40 pm

#141920

I have a strange problem that doesnt make any sense.
I have a partitioned table with 175188842 rows. Any updates to any of the rows is setting certain columns to being NULL even though they are not in the update query.
These columns that are being set to NULL are newly created columns (before my time < 2yrs ) and are Nullable even though they should be Not Null columns.
There are no triggers on the table and running a trace does not show any other process that is updating the table after the update statement.
Does any body have any ideas or has come across this in the past. Could the table be corrupt?

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please post the ddl for the table, and the complete update statement which you are currently using.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

d_s_man SSC Rookie Points: 47 More actions · Answer 2

I have just run a checktable and it returned no errors

Any updates will set these columns to NULL or the record being updated

Even this

Sales set row_update_datetime = getdate() where batch_key = 123456

The columns being set to NULL are not even listed in the update statement.

It is always the last 6 columns in the table being set to NULL. 5 are tinyint columns and the last being a varchar(40)

Do you need the create table statement? Im not sure if it would be any use?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Do you have any defaults set on the table that calculate from other columns? Do you have any triggers on this table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

d_s_man SSC Rookie Points: 47 More actions · Answer 4

There are no triggers on the table. The columns being set to null do not have any defaults and are not calculated from other columns.

Nothing makes any sense how this could be happening.

Lowell SSC Guru Points: 323546 More actions · Answer 5

is this happening directly in a SQl statement run in SSMS, or are you using something else?

for example, if you are using a typed dataset in .net, which includes all the columns of the table, but when you load the typed dataset, some columns are not included(select col1,col2, etc)

then those columns are NULL in the dataset; so if an update is issued to post the changes back to the server, columns could be set to null that way.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

d_s_man SSC Rookie Points: 47 More actions · Answer 6

The original problem was caused by an update statement in an ETL SSIS package.

I narrowed down the cause to any update statement either in SSIS or SSMS

There are about 30 other columns in the table a combination of null and not null (mostly not null) that are unchanged. Only the last 6 columns a being set to Null.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

Could you provide the DDL for the table. We are missing something here. Updates in SSMS should only affect the specified columns.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

d_s_man (9/9/2010)
I have a strange problem that doesnt make any sense.
I have a partitioned table with 175188842 rows. Any updates to any of the rows is setting certain columns to being NULL even though they are not in the update query.
These columns that are being set to NULL are newly created columns (before my time < 2yrs ) and are Nullable even though they should be Not Null columns.
There are no triggers on the table and running a trace does not show any other process that is updating the table after the update statement.
Does any body have any ideas or has come across this in the past. Could the table be corrupt?

HOW is it partitioned??? Are you using a VIEW to partition this instead of "real" partitions? If so, then that's likely the problem... you added columns but did you recompile the views? I've seen this type of mistake happen many times with views.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

d_s_man SSC Rookie Points: 47 More actions · Answer 9

Im new to Partitioning so excuse my ignorance.

From what I can see these are real partitions. There is a file for each partition with partition function and range defined.

As a work around at this stage to avoid loosing data I have altered the update statements to this table that will set the columns being set to null to themselves.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 10

At least you have a workaround. It would be nice to be able to reproduce this situation you are having so more people could look at it and maybe come up with a solution. We would need table structures and update statements that cause this issue along with some sample data - dummy data works.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 11

d_s_man (9/13/2010)
Im new to Partitioning so excuse my ignorance.
From what I can see these are real partitions. There is a file for each partition with partition function and range defined.
As a work around at this stage to avoid loosing data I have altered the update statements to this table that will set the columns being set to null to themselves.

That sounds like "real" partitioning and not the partitioned views I talked about. Guess I'm out of suggestions without being able to get closer to the problem. Sorry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)