i am using this code :
SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)
it works but returns 1 line by category >1
i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1
if 1 category has only 1 user i must not keep it
i am not shure to be clear :-)
thank you for helpingIf you need all the users fulfilling the requirements, why are you using the "max" argument. By its nature, the max (maximum) will return the largest value. Get rid of max, add user to your group by and try again.|||you mean
SELECT user, category
FROM journal
GROUP BY user,category
HAVING (COUNT(category) > 1)
i dont get it in that way
I dont need the lines : A|B where count(B) = 1
i need only the lines : A|B. C|B, D|B count(B) > 1
with my first code I get D|B (the last one)|||I don't think that your query will work it would return
where a user had the same category more than once
not differeent categories for the same user.
SELECT user
FROM journal
GROUP BY user
HAVING (COUNT(category) > 1)|||i dont get it in that way
i need for exemple 3 lines
john tennis
pierre tennis
paul tennis
3 lines if count(category) > 1|||SSELECT user_name,
category
FROM journal
WHERE category IN (SELECT category
FROM journal
GROUP BY category
HAVING COUNT(*) > 1)|||i'll try
thank you
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Has anyone ever seen this error before?
A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>
Has anyone ever seen this error before?
A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documente
d
> in the SQL Server Books Online.
>
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If yo
u
> later try to insert a row that requires more than 8060 bytes of actual dat
a,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) exceeds the maximum number of bytes per row (8060). INSERT
or UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) exceeds the maximum number of bytes per row (8060). INSERT
> or UPDATE of a row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documente
d
> in the SQL Server Books Online.
>
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If yo
u
> later try to insert a row that requires more than 8060 bytes of actual dat
a,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>
Monday, February 27, 2012
Handling errors in transactional replication
Hello,
I have this problem, I have transactional replication
setup with 2 table as article for a publisher. Consider a
transaction which inserts one row into each of these
table in the publisher database. At subscriber database
one of the commands suceeds and the other fails (lets say
because of duplicate value for a key field).
What I would like is ...
1. Enable the error to be logged to a application table.
2. Have both the commands fail (not just one)
3. Have the replication continue without erroring out
4. Have the erroneous trasction be tried at a later time.
Could help me resolving this sticky problem.
Answers on how to acheive any of the points are welcome
too.
Thanks,
Ramuk
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Thanks for the response ... though the problem I face is, that the transaction suceeds on the publisher. Its only on the subscriber that it fails.
Any ideas?
-- Paul Ibison wrote: --
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Ramul,
in theory this shouldn't be possible! If all the articles are replicated
then the same transaction should be applied.
What is the error on the subscriber?
Regards,
Paul
|||Paul,
We are in the design stage ... so the problem is yet theoretical.
The problem is when two commands which are part of the transaction are applied at the subsciber, it could be possible for one of the commands to fail (because of business logic at subscribers end) .
If I were to go the route of using "Continue on Data Consistency Errors" then the failed command will not be applied on the subscriber but the other command will ... which cause an invalid state. If I do not set "Continue on Data Consistency Errors" ... t
hen the replication would stop... that is the crux of the problem.
So an ideal solution would be for both the commands not being applied in case of an error on any one ... the error being logged into an application table (for purposes of monitoring) ... and if possible the transaction marked for retrial later.
Also another question ... can I get the value of the "Transaction sequence number" at the distributer? Can it be passed to the Custom SP's at the subscriber?
Thanks for the help,
Ramuk.
|||Ramuk,
my recollection is that if the 2 data modifications are wrapped in a
transaction which has error-trapping (or set xact_abort on) then the
transaction will be successfully completely rolled back on an error at the
subscriber. I haven't tried this out in all circumstances and AFAIR you'll
still need -SKIPERRORS. Am off home now, but will retest it out some time
tomorrow.
Regards,
Paul
I have this problem, I have transactional replication
setup with 2 table as article for a publisher. Consider a
transaction which inserts one row into each of these
table in the publisher database. At subscriber database
one of the commands suceeds and the other fails (lets say
because of duplicate value for a key field).
What I would like is ...
1. Enable the error to be logged to a application table.
2. Have both the commands fail (not just one)
3. Have the replication continue without erroring out
4. Have the erroneous trasction be tried at a later time.
Could help me resolving this sticky problem.
Answers on how to acheive any of the points are welcome
too.
Thanks,
Ramuk
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Thanks for the response ... though the problem I face is, that the transaction suceeds on the publisher. Its only on the subscriber that it fails.
Any ideas?
-- Paul Ibison wrote: --
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Ramul,
in theory this shouldn't be possible! If all the articles are replicated
then the same transaction should be applied.
What is the error on the subscriber?
Regards,
Paul
|||Paul,
We are in the design stage ... so the problem is yet theoretical.
The problem is when two commands which are part of the transaction are applied at the subsciber, it could be possible for one of the commands to fail (because of business logic at subscribers end) .
If I were to go the route of using "Continue on Data Consistency Errors" then the failed command will not be applied on the subscriber but the other command will ... which cause an invalid state. If I do not set "Continue on Data Consistency Errors" ... t
hen the replication would stop... that is the crux of the problem.
So an ideal solution would be for both the commands not being applied in case of an error on any one ... the error being logged into an application table (for purposes of monitoring) ... and if possible the transaction marked for retrial later.
Also another question ... can I get the value of the "Transaction sequence number" at the distributer? Can it be passed to the Custom SP's at the subscriber?
Thanks for the help,
Ramuk.
|||Ramuk,
my recollection is that if the 2 data modifications are wrapped in a
transaction which has error-trapping (or set xact_abort on) then the
transaction will be successfully completely rolled back on an error at the
subscriber. I haven't tried this out in all circumstances and AFAIR you'll
still need -SKIPERRORS. Am off home now, but will retest it out some time
tomorrow.
Regards,
Paul
Sunday, February 19, 2012
Halloween Problem
Hi,
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive/2006/01/31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> According to this page:
>> http://support.microsoft.com/kb/294860/EN-US
>> Halloween problem occurs where the physical location of a row within a
>> table changes due to a modification operation. As a result, the same row
>> may be revisited multiple times within the context of a single logical
>> operation.
>> I found an example of Halloween problem in this page:
>> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
>> In this example, physical location of a row does not change during the
>> update operation. The "test" table has one index (clustered). The rows
>> will be moved only if the clustered key is updated (which does not occur
>> here).
>> 1) How does Halloween problem happen here?
>> 2) Why Loop Join is not encountered with this problem?
>> Thanks in advance,
>> Leila
>
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive/2006/01/31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> According to this page:
>> http://support.microsoft.com/kb/294860/EN-US
>> Halloween problem occurs where the physical location of a row within a
>> table changes due to a modification operation. As a result, the same row
>> may be revisited multiple times within the context of a single logical
>> operation.
>> I found an example of Halloween problem in this page:
>> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
>> In this example, physical location of a row does not change during the
>> update operation. The "test" table has one index (clustered). The rows
>> will be moved only if the clustered key is updated (which does not occur
>> here).
>> 1) How does Halloween problem happen here?
>> 2) Why Loop Join is not encountered with this problem?
>> Thanks in advance,
>> Leila
>
Halloween Problem
Hi,
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
Leila
Is there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>
|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive/...31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
Leila
Is there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>
|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive/...31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
Halloween Problem
Hi,
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive.../31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive.../31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
Halloween Problem
Hi,
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive.../31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
According to this page:
http://support.microsoft.com/kb/294860/EN-US
Halloween problem occurs where the physical location of a row within a table
changes due to a modification operation. As a result, the same row may be
revisited multiple times within the context of a single logical operation.
I found an example of Halloween problem in this page:
http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
In this example, physical location of a row does not change during the
update operation. The "test" table has one index (clustered). The rows will
be moved only if the clustered key is updated (which does not occur here).
1) How does Halloween problem happen here?
2) Why Loop Join is not encountered with this problem?
Thanks in advance,
LeilaIs there a practical reason for this question, or is it an academic one? In
other words, do you have an update where you think you are seeing the
Halloween Problem?
The Halloween Problem is a classic database problem wherein the membership
in the set you are reading is changed by your own update operation, causing
you to see the same row repeatedly. The theoretical problem is independent
of the underlying rdbms implementation. The explanation in the first KB
article is incomplete in describing the problem. For example, even if rows
did not actually move a query plan that referenced any index incorporating a
column that is also being modified might be subject to the HP. In early
rdbms products it was up to the user to avoid making update requests that
would cause the Halloween Problem. In newer products, such as SQL Server
7.0/2000/2005 the Query Optimizer takes care of the problem via a technique
called Halloween Protection. When it sees that the update you are
performing could change one of the inputs it generates a plan that won't
have that problem. For example, it will spool the impacted inputs to a
temporary workfile before performing any updates and then read from the
temporary workfile rather than the underlying data that is being updated..
The second KB article is just pointing out a situation where the Query
Optimizer was not producing the correct plan. I didn't look at it in depth,
but I suspect that the reason Loop Join didn't have the problem is that the
optimizer was generating the correct Halloween Protection for that
situation.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
> Hi,
> According to this page:
> http://support.microsoft.com/kb/294860/EN-US
> Halloween problem occurs where the physical location of a row within a
> table changes due to a modification operation. As a result, the same row
> may be revisited multiple times within the context of a single logical
> operation.
> I found an example of Halloween problem in this page:
> http://www.kbalertz.com/Feedback.aspx?kbNumber=285870
> In this example, physical location of a row does not change during the
> update operation. The "test" table has one index (clustered). The rows
> will be moved only if the clustered key is updated (which does not occur
> here).
> 1) How does Halloween problem happen here?
> 2) Why Loop Join is not encountered with this problem?
> Thanks in advance,
> Leila
>|||Thanks Hal,
Actually yes! This is an academic question which I'm interested(curious) in
its answer.
By your explanation I conclude that HP can occur when:
A) Rows are relocated (in index for example) and the update is using that
index
(http://blogs.msdn.com/ianjo/archive.../31/521078.aspx)
B) Update command accesses the rows that have been updated during itself
(which must be isolated)
According to KBs, HP is eliminated after installing SQL Server 2000 SP1. I
was trying this on a instance without SP.
The plan of update command in second KB which I mentioned is different from
instance with SP (This sample causes item B).
But what ever I tried to simulate HP by the cause of rows' relocation (item
A), the plan used a table spool which I think prevents HP (even no SP was
applied).
Do you have any sample that simulates that (Maybe my own experminet is
wrong)? or type A is always detected by the query optimizer and is prevented
even without SP?
I also include my code that used for testing item A:
--
use tempdb
go
drop table emp
go
create table emp(
eid int primary key,
salary int)
go
insert emp select 1,100
insert emp select 2,200
insert emp select 3,300
insert emp select 4,400
create index a on emp(salary)
-- Halloween=Yes, Solved by Table Spool
update emp
set salary=salary*1.1
from emp emp2 with(index(a))
where eid=emp2.eid
-- Halloween=No, Because index 'a' is not used
update emp
set salary=salary*1.1
from emp emp2
where eid=emp2.eid
-- Halloween=Yes, Solved by Sort
update emp
set eid=eid*1.1
--
Thanks,
Leila
"Hal Berenson" <hberenson@.predictableit.com> wrote in message
news:eIWuOTSVGHA.5652@.TK2MSFTNGP09.phx.gbl...
> Is there a practical reason for this question, or is it an academic one?
> In other words, do you have an update where you think you are seeing the
> Halloween Problem?
> The Halloween Problem is a classic database problem wherein the membership
> in the set you are reading is changed by your own update operation,
> causing you to see the same row repeatedly. The theoretical problem is
> independent of the underlying rdbms implementation. The explanation in
> the first KB article is incomplete in describing the problem. For
> example, even if rows did not actually move a query plan that referenced
> any index incorporating a column that is also being modified might be
> subject to the HP. In early rdbms products it was up to the user to avoid
> making update requests that would cause the Halloween Problem. In newer
> products, such as SQL Server 7.0/2000/2005 the Query Optimizer takes care
> of the problem via a technique called Halloween Protection. When it sees
> that the update you are performing could change one of the inputs it
> generates a plan that won't have that problem. For example, it will spool
> the impacted inputs to a temporary workfile before performing any updates
> and then read from the temporary workfile rather than the underlying data
> that is being updated..
> The second KB article is just pointing out a situation where the Query
> Optimizer was not producing the correct plan. I didn't look at it in
> depth, but I suspect that the reason Loop Join didn't have the problem is
> that the optimizer was generating the correct Halloween Protection for
> that situation.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uIddyxRVGHA.4884@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)