What is a "self-update query" - 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

General SQL Server Forums

Data Corruption Issues

What is a "self-update query"

Author

Topic

mgottli
Starting Member

4 Posts

Posted - 2005-10-18 : 11:51:47

I am getting an index corruption error:

Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'TAP', index 'patient.IX_patient' (ID 172631758) (index ID 32). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:614994:261) with values (recent_admission_seq = 3540364 and patient_seq = 696709) points to the data row identified by ().

This exact error is discussed in the article http://support.microsoft.com/kb/884856 and fixed in service pack 4.

However, it says that this error can be triggered when you perform a "self-update query." What is a that? Is that a query in the form UPDATE ... SELECT ... ? I want to see if we are doing what they are talking about so I can determine if this is the cause of our problem.

Thank you!!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-18 : 12:32:37

Are you seeing any other errors returned by CHECKDB? (Just wondering how you're sure that this is the problem you've hit)

Here's an example:

create table test (i1 int, i2 int)
go

declare @i int
set @i = 0
while (@i < 3001)
begin
insert test (i1, i2) values (@i / 2500, @i)
set @i = @i + 1
end
go

declare @n int
set @n = 246
update test set @n = i2 = @n + 1
where i1 = 1
go

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

mgottli
Starting Member

4 Posts

Posted - 2005-10-18 : 13:23:47

The only errors in DBCC CHECKDB was that a table had consistance errors. I then ran DBCC CHECKTABLE ('table_name') and got the above errors.

I ran your query and I see what it is doing, but what about your UPDATE statement makes it a "self-update query" ? Can you provide a definition of what a self-update query is?

Thank you

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-18 : 18:25:16

Actually the KB article is using a misleading term - its really what we call a 'simple update'.

A simple update is an optimization that we use to collapse the read and write cursors of a DML plan into a single operator. It's not something that we document although we don't hide it either (since you can tell from showplan that we have a simple update by observing whether the update operator has any children)

Hope this helps.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

mgottli
Starting Member

4 Posts

Posted - 2005-10-20 : 09:59:59

Thanks Paul. So if a simple update is an optimization, is there any way for me to determine if it is used in my database short of looking at the execution plan for every update query?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-20 : 17:43:39

No - that's the only way.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources