We should avoid using cursors whereever we can as Cursors take much of the SQL Server Resources creating performance issues. checkout one trick here to implement this:
Note: replace the table, column names with your respective table,column names.
To Select Multiple Row Values without using
cursors:
====================================================
DECLARE @VariableName TABLE
(SM_ID int PRIMARY KEY, SM_MisType char(3), SM_Grade
INT)
Declare @sm_id Varchar(20)
DECLARE @var1 varchar(300),@var2 varchar(300)
SET ROWCOUNT 0
INSERT @VariableName Select SM_ID,SM_MisType,SM_Grade
from scMission_Master
SET @var1=''
SET @var2=''
SET ROWCOUNT 1
Select @sm_id = sm_id from @VariableName
While @@rowcount <> 0
Begin
Select @var1=SM_ID from @VariableName Where
sm_id = @sm_id
SET @var2=@var2+@var1+'~'
Delete from @VariableName where sm_id = @sm_id
Select @sm_id = sm_id from @VariableName
End
SELECT @var2
Set Rowcount 0
--table variables are cleaned up automatically at the
end of the function,
--stored procedure, or batch in which they are
defined.
--Table variables used in stored procedures result
--in fewer recompilations of the stored procedures
than when temporary tables are used.
|
| Author: Kevin Lang 26 Oct 2004 | Member Level: Bronze Points : 0 |
While the intent is admirable, those not thoroughly understanding the underpinnings of the data engine will be mislead. This "trick" replaces a tuned feature of SQL Server (a cursor) that has been designed to balance the costs of I/O, Memory, and CPU, with a memory structure (a Table Variable) that may or may not end up being significantly more expensive to use in this particular manner. For a high-volume application worthy of an attempt to circumvent SQL Server's "smarts" and features (a cursor), this technique would most likely result in a measurable increase in CPU and Memory consumption versus a cursor-based implementation. Another significant problem is that keys that may have made it into the temporary "table" may no longer exists in the database by the time they are processed by the loop because the real-time locking mechanism of the database engine have been circumvented.
Again, kudos for the ingenuity and the intent, but for those who may be drawn to a seemingly "cool" replacement for a cursor should take the time to measure the true cost of both solutions before implementing either one in a Production environment.
|
| Author: Kevin Marshbank 27 Oct 2004 | Member Level: Bronze Points : 0 |
I have to disagree with Kevin Lang.
In SQL Server cursors only exist for those that do not know SQL well enough to code without it.
First off, your keys should never disappear or change. If they do then that's a whole different issue that needs to be addressed with a design change.
Second, a high-volume application had better not use a cursor! Cursors are extremely poor in performance and so far I have not found a cursor I could not get rid of in exchange for properly coded, set-based T-SQL code resulting in faster, less resource intensive operation.
Regarding a WHILE loop: Usually faster, most certainly less resource intensive than a cursor. As long as you know what you are doing with locks, and as Kevin Lang pointed out, your keys, you should be fine. One caveat, a WHILE loop is sometimes only marginally better than a cursor and is still a loop which is almost never needed in SQL.
Here is my alternative to the provided code(I changed the table to sysobjects so anyone can run this as an example):
DECLARE @ID_List varchar(1000)
/* so it's not NULL which would make the end result NULL */
SET @ID_List = ''
SELECT @ID_List = @ID_List + cast(id as varchar) + '~'
FROM sysobjects WITH(NOLOCK)
SET @ID_List = LEFT(@ID_List, len(@ID_List)-1) /* get rid of trailing "~" */
SELECT @ID_List
|