Deferred Updates versus In-Place Updates

The following question was posted on http://support.microsoft.com/kb/195760/en-us:

Q. What are the conditions under which deferred updates occur in SQL Server 7.0?

A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.

You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.

NOTE: This behavior holds true for any index.

Has this behavior changed in SQL Server 2005?

Ken, whenever you are modifying the key columns of an index, the operation will be implemented as a delete followed by an insert, as the row needs to move within the B-Tree from the old location to the new. This can be explictly visible in the query plan showed in SHOWPLAN with the SPLIT operator, or be implicitly performed at the Storage Engine level. In SQL 2005, the number of cases where the SPLIT operator is employed is somewhat decreased, but it is still used. See the following trivial example:

use tempdb
go

create table t(p int primary key clustered)
go

update t set p = p + 1
go

Please let me know if you have more questions on this.
Thanks

|||

Okay, that tells me how changes to the primary key columns will be processed. Now what will happen if only the non-key columns are changed?

Ken

|||When only non key columns are updated, changes are performed in place, i.e. without moving the row inside the B-Tree. This is of course a much cheaper operation. You might want to take a look at a new SQL 2005 feature, the ability to include nonkey columns in nonclustered indexes.

Bottom line: an update statement will not maintain the nonclustered indexes whose columns are not being modified, will trigger row movements inside the indexes whose keys are being updated, and perform in place update to indexes whose only non key columns are being modified.

Deferred Updates versus In-Place Updates

The following question was posted on http://support.microsoft.com/kb/195760/en-us:

Q. What are the conditions under which deferred updates occur in SQL Server 7.0?

A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.

You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.

NOTE: This behavior holds true for any index.

Has this behavior changed in SQL Server 2005?

Ken, whenever you are modifying the key columns of an index, the operation will be implemented as a delete followed by an insert, as the row needs to move within the B-Tree from the old location to the new. This can be explictly visible in the query plan showed in SHOWPLAN with the SPLIT operator, or be implicitly performed at the Storage Engine level. In SQL 2005, the number of cases where the SPLIT operator is employed is somewhat decreased, but it is still used. See the following trivial example:

use tempdb
go

create table t(p int primary key clustered)
go

update t set p = p + 1
go

Please let me know if you have more questions on this.
Thanks

|||

Okay, that tells me how changes to the primary key columns will be processed. Now what will happen if only the non-key columns are changed?

Ken

|||When only non key columns are updated, changes are performed in place, i.e. without moving the row inside the B-Tree. This is of course a much cheaper operation. You might want to take a look at a new SQL 2005 feature, the ability to include nonkey columns in nonclustered indexes.

Bottom line: an update statement will not maintain the nonclustered indexes whose columns are not being modified, will trigger row movements inside the indexes whose keys are being updated, and perform in place update to indexes whose only non key columns are being modified.

deferred updates in SQL2k (replication)

Hallo!
Are deferred updates possible in MSSQL2k?
I don't found anything in BOL.
Thanks Thomas
Would need some more info as to what you require, but outside of SQL Server
you can use message queuing to defer the update. In SQL Server replication
we have queued updatable subscribers, where the queue can be a sql table or
a MSMQ system. At a more simplistic level, all replication involves deferred
updates to the subscriber, as there is no zero latency technique. The degree
of deferral can be controlled by editing the agent's profile and by altering
the agent schedule.
Regards,
Paul Ibison
|||On Thu, 13 May 2004 09:08:53 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Would need some more info as to what you require, but outside of SQL Server
>you can use message queuing to defer the update. In SQL Server replication
>we have queued updatable subscribers, where the queue can be a sql table or
>a MSMQ system. At a more simplistic level, all replication involves deferred
>updates to the subscriber, as there is no zero latency technique. The degree
>of deferral can be controlled by editing the agent's profile and by altering
>the agent schedule.
>Regards,
>Paul Ibison
>
In some circumstances in MSSQL 6.5 an UPDATE will changed in
DELETE/INSERT-pair. In a bidirectional replication scenario with
vertical partitions and a special application the need of custom sp
triggers was required.
After 5 years of successful bidirectional replication I will change to
MSSQL2k.
I would know, if this in MSSQL2k similar?
If there are no deferred updates, then I needn't custom triggers or
dynamic filters (new feature in 2k).
thanks in advance
|||Thomas,
the same problem still exists in SQL 2000, although for singleton updates
you can use a trace flag to apply an update on the subscriber
(http://support.microsoft.com/default...microsoft.com
:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1)
HTH,
Paul Ibison

Deferred Updates (Singleton Updates)

How is deferred update implemented in SQL 2005 Replication? I have tried to
set the trace flag 8207 like in SQL 2000, but this is not working.
Thanks,
Andrew
MCSA,MCDBA
Hi Andrew - I've just tried adding the trace flag and it is added OK as
confirmed by "dbcc tracestatus". Did you have a problem adding it or was the
problem later on?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
Regards
Hello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred Name Resolution gone wild.

I have a script that has a spelling error in the insert statement:
insert #tempTable(id, statux) values (1, 'this is a test')
This statement is deep in the script behind If and Case..when statements
and is never ever executed. There is a validation, which prevents this
code from executing, that hasn't been hit in a very long time.
That said, I recently ran this script on a sql server 2000 machine, and
the script failed with an error saying that 'statux' was an invalid
column. The odd thing is that the failure occured when the code
execution got close to the offending code statement. It's like SQL
Server decided to recompile the piece of code where the insert statement
is located.
I ran the same script on a different sql server 2000 and sql server 2005
machines and the script did not fail.
Of course, I fixed the misspelling, but I am curious about the deeper
issue of how and when Sql Server enforces Deferred Name Resolution (if
that is what causes the issue).
Is there a setting that controls this issue?
RegardsHello Frank,
I understand that you have some concerns about deferred name resolution.
When a stored procedure is created, the statements in the procedure are
parsed for syntactical accuracy. If a syntactical error is encountered in
the procedure definition, an error is returned and the stored procedure is
not created. If the statements are syntactically correct, the text of the
stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor
reads the text of the stored procedure from the syscomments system table of
the procedure and checks that the names of the objects used by the
procedure are present. This process is called deferred name resolution
because table objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed. You may
want to refer to the following article for details:
http://msdn2.microsoft.com/en-us/library/aa214346(SQL.80).aspx
It seems that when the SQL is first executed the column is valid and the
compliation completed. However, when the exectuion plan is run again on
this statement, the error appears because the column is actually changed.
Please let's know if you have any further comments or questions. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column.
To add to Perter's response, the column name will be validated only if the
table exists when the proc is created.

> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
My guess is that the temp table existed on only the one server. The script
below illustrates this.
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
GO
--this create will fail
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
DROP TABLE #tempTable
GO
--this create will succeed
CREATE PROC dbo.Test1
AS
CREATE TABLE #tempTable
(
id int NOT NULL,
status varchar(30) NOT NULL
)
INSERT #tempTable(id, statux)
VALUES (1, 'this is a test')
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:OAwRUA2ZHHA.984@.TK2MSFTNGP04.phx.gbl...
>I have a script that has a spelling error in the insert statement:
> insert #tempTable(id, statux) values (1, 'this is a test')
> This statement is deep in the script behind If and Case..when statements
> and is never ever executed. There is a validation, which prevents this
> code from executing, that hasn't been hit in a very long time.
> That said, I recently ran this script on a sql server 2000 machine, and
> the script failed with an error saying that 'statux' was an invalid
> column. The odd thing is that the failure occured when the code execution
> got close to the offending code statement. It's like SQL Server decided
> to recompile the piece of code where the insert statement is located.
> I ran the same script on a different sql server 2000 and sql server 2005
> machines and the script did not fail.
> Of course, I fixed the misspelling, but I am curious about the deeper
> issue of how and when Sql Server enforces Deferred Name Resolution (if
> that is what causes the issue).
> Is there a setting that controls this issue?
> Regards

Deferred constraints?

Is this on the list for Katmai? (BOL shows only what is available in the current CTP, so it is hard to know).

thanks

This is not planned for Katmai. -- Umachandar Jayachandran Microsoft SQL Server Performance Team SQL Server Engine Team Tips Blog at http://blogs.msdn.com/sqltips/default.aspx This posting is provided "AS IS" with no warranties, and confers no rights. "Lakusha"@.discussions.microsoft.com wrote on Thu, 12 Jul 2007 07:11:08 -0700: L> Is this on the list for Katmai? (BOL shows only what is available in L> the current CTP, so it is hard to know). L> thanks

Deferred constraints sql server 2005

Is there any way to defer constriants in sql server 2005? I have found some sites that say use the keyword deferred but that always give me an "Incorrect syntax near 'deferred' "error.

Hi Jeff,

Can you expand on the meaning of "deferred constraint", please?

If what you are trying, is to disable a constraint to do some bulk operations and then enable it back, then see "alter table" in BOL.

with check / nocheck --> is to check or nocheck the data when you enable the constraint

check / nocheck --> to enable / disable the constraint

Example:

use tempdb

go

create table dbo.t1(

c1 int constraint ck_c1 check (c1 > 0)

)

go

insert into dbo.t1 values(-1)

go

alter table dbo.t1

nocheck constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsDisabled')

go

insert into dbo.t1 values(-1)

go

select * from dbo.t1

go

alter table dbo.t1

with nocheck check constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')

go

-- will give error because there is a value breaking the constraint

alter table dbo.t1

with check check constraint ck_c1

go

delete dbo.t1 where c1 = -1

go

alter table dbo.t1

with check check constraint ck_c1

go

select objectproperty(object_id('ck_c1'), 'CnstIsNotTrusted')

go

drop table dbo.t1

go

Be careful when enabling the constraint back, remember to use "with check" to make is trusted. The query optimizer uses constraints when choosing the execution plan, but they need to be trusted.

AMB