Understanding Variable Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Understanding Variable Update

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-16 : 12:47:11

Yesterday I was working to try to resolve a issue with not Using a cursor for a unique type of update.

TG was able to resolve my issue using this query


Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY Clustered, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage(StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai )
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select '2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0
Union all
select '2009-12-01 00:00:00.000',	5,	0.00,	0.00,	0.0900,	0
Union all
select '2010-12-01 00:00:00.000',	6,	0.00,	0.00,	0.0200,	0

declare @ai decimal(10,4)
       ,@aiTot decimal(10,4)
       ,@a money
       ,@ia money

update s set
       @ai = ai = s.interest * (isNull(@aiTot,0) + @a + @ia)
       ,@a = isNull(@a, 0) + s.amount
       ,@ia = isNull(@ia, 0) + s.interestamount
       ,@aiTot = isNull(@aiTot, 0) + @ai
from   @stage s

TG pointed out that although this query should always work, there is a underlying issue in this type of update method, due to it is dependent on the order in which sql updates (Currently the clustered index is the only way I know of to secure the order is in tact).

Although it is unlikely, TG pointed out that do to this inherent flaw in the methodology, if future releases of SQL were to be modified to improve performance in updates, this method COULD possibly not work, and there is no 100% guarentee that the index would be used.

My questions are

1. Is there any way to Ensure that the style of update I am using uses the clustered index (i.e. "from @stage s WITH (INDEX = ...)") but I do not know the ID of the index since it is a TMP table?

2. Force the order of the update to be the order of the index (Just because there is a index, that doesn't ensure that a future release of sql will reference the index from top to bottom (I know this is unlikely, but I still would like to know if there was a way to ensure the update did always occur from top to bottom of the index).

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-16 : 13:22:13

You might want to read this excellent article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-16 : 13:22:49

This may not be a direct answer, but.... There is NO way to guarantee order without specifying an ORDER BY clause, end of story. However, there is a good chance that the data will be affected or returned in the clustered index column order. Which is why almost every database professional I talk with thinks that data is ALWAYS returned in the PK order if an ORDER BY is omitted.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-16 : 13:33:26

quote:
Originally posted by Lamprey

This may not be a direct answer, but.... There is NO way to guarantee order without specifying an ORDER BY clause, end of story. However, there is a good chance that the data will be affected or returned in the clustered index column order. Which is why almost every database professional I talk with thinks that data is ALWAYS returned in the PK order if an ORDER BY is omitted.

The article and, in particular, the related comments/discussion goes into this stuff AT LENGTH. If you've got the patience for it, I definitely recommend reading and understanding as much as you can.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-16 : 15:49:30

Just curious, how do you specify the index on a tmp table

(i.e. "from @stage s WITH (INDEX = 1)") Is it possible?

Can someone please confirm that the query below is written as accurate as possible for this update clause(I need the table variables due to this is part of a function). I added a reference variable and set it to the clustered index column to ensure the sort based on the article from Randall.

If anyone sees a issue, please let me know.



Declare @Stage Table(RowID INT PRIMARY KEY  Clustered,StartDate datetime,BenefitInterestID int , Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))

Insert Into @Stage(RowID,StartDate ,BenefitInterestID, Amount, InterestAmount, Interest , ai )
Select
1,convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,	1 as BenefitInterestID,1701.00 as amount,	79.605 as InterestAmount ,	0.1000 as Interest,	0.0000 as ai
Union all
select 2,'2007-12-01 00:00:00.000',	2,	172.80,	7.92,	0.0500,	0
Union all
select 3,'2008-12-01 00:00:00.000',	4,	0.00,	0.00,	0.0700,	0
Union all
select 4,'2009-12-01 00:00:00.000',	5,	0.00,	0.00,	0.0900,	0
Union all
select 5,'2010-12-01 00:00:00.000',	6,	0.00,	0.00,	0.0200,	0

declare @ai decimal(10,4)
       ,@aiTot decimal(10,4)
       ,@a money
       ,@ia money
		,@Referance int

update s set
       @ai = ai = s.interest * (isNull(@aiTot,0) + @a + @ia)
       ,@a = isNull(@a, 0) + s.amount
       ,@ia = isNull(@ia, 0) + s.interestamount
       ,@aiTot = isNull(@aiTot, 0) + @ai
		,@Referance = s.RowID
from   @stage s 

select * from @Stage

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 16:27:46

You can't create indexes on table variables, therefore you can't use an index hint.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-16 : 16:42:25

Actually, Vinnie, I'm sure you noticed that my solution included this switch to a #temp table so I could use the clustered index:

select * into #stage from @stage
alter table #stage add primary key clustered (benefitInterestID)

then changed my table to #stage rather than @stage

And as I mentioned (or tried to imply), that a single table with a clustered index and those anchor SETs seemed to be all that was requiered to get consistant results updated in order of the key. A hint was not necessary.

orig topic in case anyone is interested:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102885

Ryan, thanks for the link. I was hoping you'd chime in with some words of wisdom because I've been curious about that. I haven't read it yet but here goes...

Lamprey, "There is NO way to guarantee order without specifying an ORDER BY clause"
those are words to live by - that is why I qualified my solution :)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-16 : 17:22:22

Just read the article - nice. Yep it was Jeff Moden that convinced me that this technique was "safe" to use in some topic here awhile back.

I liked the comment (from alzdba) about watch out for multi-threading efficiencies so he suggested: maxdop=1
good thought.

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-16 : 17:47:28

Do you have any info on the INDEX hint? I cannot find anything in BOL that says it will guarantee any order. It may well cause SQL to operate in the correct order but it seems like luck to me. I think Jeff's solution is very cool, but I don't think I would ever use it in production code though.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-16 : 21:19:46

The code I posted needs to be part of a function, that is why I am using table variables rather then Tmp tables.

It was my understanding that you can create a clustered index on a table Variable, just not a Non-Clustered. I was able to add a clustered index (As illustrated by the sample code I posted) and the execution plan DOES show that the clusterd index is being used.

Everything appears to work the same on my Table Variable as it would on a Tmp table, but Should my query in theory still function as expected? Is there anything I am missing?

It is very important that the function works as expected (It's a calcuation relating to money we're paying out), so I would like everyones opinion. What is the verdict, should I use a loop instead and sacrafice the speed for the peace of mind?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-19 : 14:18:09

>>What is the verdict, should I use a loop instead and sacrafice the speed for the peace of mind?
Well I still don't know some things like:
- why you think it needs to be in a function
- will the process be based on a client/customer/user action and will this person be waiting for it to complete?

But personally I would stick with the loop, maybe do a redesign so user's aren't "waiting" for processing
and have the "piece of mind" that the updates will always happen in the correct sequence.

If you went with this technique I would stick with what has been "proven" by Jeff Moden and use a #temp or permanent table with a clustered index and the other prerequisits I mentioned in the other thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102885
He did a lot of testing to prove it was safe but none of his testing involved table variables and functions.

Be One with the Optimizer
TG

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-19 : 17:13:48

I guess nothing NEEDS to be a certain way, but this is in a function because it is calculating the amounts in members accounts (200,000+ members) , and I would like to be able to run as such

Select m.*
,dbo.MyFunction(m.MemberID) as AccountAmount
from mymembers m

This is used for reporting and various other needs.

Although there are scenarios where it will be based on user action (i.e. checking a individual account rather then the entire member list), in that case speed is not a issue.

TG, I appreciate all your help. You really have saved me a lot of head aches. I see your point about using tmp tables, and will consider revising.

I still would like to get other peoples opinions as well on whether the @Table variables should work the exact same as the #tmp's. I will in the next few days re-due the code in http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
to use table variables instead of #tmps to see if it passes the tests the same way. I will post the results.

Thanks to everyone for the assistance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources