Indexing. - 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 2000 Forums

Transact-SQL (2000)

Indexing.

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-25 : 00:37:29

Dear All.
Here i am using the temp table. But i can't take a decission put an index for this table. i am updating some what , here i present that.
please suggest me the index for the table #tmp_one
CREATE TABLE #tmp_one
( sno smallint null,
h01_emp_num EMPNUM null,
amount varchar(255) null,
head varchar(255) null,
elhead varchar(255) null,
total varchar(255) null,
page_total cmn_pamt null,
element varchar(8) null,
tempamount cmn_pamt null,
amountlen smallint null,
stramount varchar(15) null,
sumstr varchar(15) null
)

*********************************************************************************************

UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno

UPDATE #tmp_one
SET head = SPACE(@extractlength - 5) + 'TOTAL'
WHERE sno = @sno

UPDATE #tmp_one
SET elhead = SPACE(@extractlength - 9) + 'CAR. OVER'
WHERE sno = @sno

UPDATE #tmp_one
SET amount = STUFF(STR(#t4.page_total, @extractlength, @pamt),@extractlength - 3 - @pamt,0,',')
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) > 4 + @pamt
AND DATALENGTH(LTRIM(STR(#t4.page_total,10,2))) < 8 + @pamt
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STR(#t4.page_total, @extractlength - 2, @pamt)
FROM #tmp_four #t4
WHERE #t4.sno = @sno
AND #tmp_one.H01_Emp_Num = #t4.H01_Emp_Num
AND #tmp_one.sno = #t4.sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) < 5 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 5 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,1,0,' ')
WHERE DATALENGTH(LTRIM(amount)) > 4 + @pamt
AND DATALENGTH(LTRIM(amount)) < 9 + @pamt
AND #tmp_one.sno = @sno

UPDATE #tmp_one
SET amount = STUFF(amount,@extractlength - 8 - @pamt,0,',')
WHERE DATALENGTH(LTRIM(amount)) > 8 + @pamt
AND #tmp_one.sno = @sno

SELECT @noofcol = @noofcol + 1

UPDATE #tmp_one
SET head = head + SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = #tmp_four.page_total
FROM #tmp_four
WHERE #tmp_four.sno = @sno
AND #tmp_one.H01_Emp_Num = #tmp_four.H01_Emp_Num

UPDATE #tmp_one
SET elhead = elhead + SPACE(@extractlength - @len) + LTRIM(RTRIM(@element))
WHERE sno = @sno

UPDATE #tmp_one
SET element = @element
WHERE sno = @sno

/*Code added by bala - to nullify the tempamount before updating the value - because in case if there is no value in p21, then the previous value (for the previous element remain there)*/
UPDATE #tmp_one
SET tempamount = 0.0

UPDATE #tmp_one
SET tempamount = ISNULL(T3.P21_Actual_Rate,0.0)
FROM #tmp_three T3, #tmp_one T1
WHERE T1.H01_emp_num = T3.H01_Emp_num
AND T3.P09_Element_Code = T1.element
AND T1.sno = @sno

UPDATE #tmp_one
SET amountlen = DATALENGTH(LTRIM(RTRIM(STR(tempamount,@extractlength,@pamt))))
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STR(tempamount,@extractlength - 2,@pamt)
WHERE sno = @sno

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen < 5 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 5 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 4 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,1,0,' ')
WHERE sno = @sno
AND amountlen > 4 + @pamt
AND amountlen < 8 + @pamt

UPDATE #tmp_one
SET stramount = STUFF(stramount,@extractlength - 8 - @pamt,0,',')
WHERE sno = @sno
AND amountlen > 7 + @pamt

UPDATE #tmp_one
SET amount = amount + stramount
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = page_total + tempamount
WHERE sno = @sno

Thanks
KK

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-25 : 01:05:24

sno

--------------------
keeping it simple...

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-25 : 01:11:31

thanks jen

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-25 : 06:20:41

quote:

UPDATE #tmp_one
SET head = SPACE(@extractlength - 8) + 'EARNINGS'
WHERE sno = @sno

UPDATE #tmp_one
SET page_total = 0
WHERE sno = @sno

UPDATE #tmp_one
SET amount = RTRIM('')
, head = RTRIM('')
, elhead = RTRIM('')
, total = RTRIM('')
, element = RTRIM('')
, stramount = RTRIM('')
, sumstr = RTRIM('')
WHERE sno = @sno

You can also combined most of the UPDATE statement into single statement. And you don't need to perform rtrim('') on empty string.

UPDATE #tmp_one
SET    head       = SPACE(@extractlength - 8) + 'EARNINGS',
       page_total = 0,
       amount     = '',
       head       = '',
       elhead     = '',
       total      = '',
       element    = '',
       stramount  = '',
       sumstr     = ''
WHERE  sno = @sno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources