January 4, 2010 at 11:19 am
can someone enlighten me to understanding keyset cursors? I cannot comprehend what they are.
this is my understanding thus far but it doesn't register in my head.......
keyset cursors is an alternative to dynamic and static cursors based on keys in a table? it is able to update data that is like dynamic and uses a static strategy....
am i right or completely off?
January 4, 2010 at 12:39 pm
KEYSET - specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 5, 2010 at 6:18 am
I believe that you should NEVER use a cursor of any type. There is always a better way.
Andrew SQLDBA
January 5, 2010 at 2:58 pm
AndrewSQLDBA (1/5/2010)
I believe that you should NEVER use a cursor of any type. There is always a better way.Andrew SQLDBA
Good Advice
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2010 at 11:21 am
AndrewSQLDBA (1/5/2010)
I believe that you should NEVER use a cursor of any type. There is always a better way.Andrew SQLDBA
Hmmm...never is a strong word. For updating data I agree, never update data with a cursor. I could endorse the use of cursors when calling an external process. Example, using dtexec to invoke a SSIS package while dynamically passing variables to the package. I may just be attempting to justify my previous design decisions. I guess I'm more of a relativist than an absolutist. 🙂
January 7, 2010 at 11:29 am
I am an absolutist, never say never.
January 7, 2010 at 11:43 am
emily-1119612 (1/7/2010)
AndrewSQLDBA (1/5/2010)
I believe that you should NEVER use a cursor of any type. There is always a better way.Andrew SQLDBA
Hmmm...never is a strong word. For updating data I agree, never update data with a cursor. I could endorse the use of cursors when calling an external process. Example, using dtexec to invoke a SSIS package while dynamically passing variables to the package. I may just be attempting to justify my previous design decisions. I guess I'm more of a relativist than an absolutist. 🙂
True - there may be a rare occasion where a cursor may not perform any worse than a set-based query. However, once it starts becoming permissible in some areas, it becomes very difficult to prevent developers from trying to implement a cursor in another area.
As a general rule, cursors are bad - set based is good.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 10, 2010 at 5:28 am
It's not always about the performance of one query.
Quite frequently, concurrency is more important, in which case a cursor is a fine choice.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply