Why is it considered bad practice to use cursors in SQL Server?

by

Because cursors take up memory and create locks.

What you are really doing is attempting to force set-based technology into non-set based functionality. And, in all fairness, I should point out that cursors do have a use, but they are frowned upon because many folks who are not used to using set-based solutions use cursors instead of figuring out the set-based solution.

But, when you open a cursor, you are basically loading those rows into memory and locking them, creating potential blocks. Then, as you cycle through the cursor, you are making changes to other tables and still keeping all of the memory and locks of the cursor open.

All of which has the potential to cause performance issues for other users.

So, as a general rule, cursors are frowned upon. Especially if that’s the first solution arrived at in solving a problem.

More Related Contents:

  1. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  2. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  3. Parameter Sniffing (or Spoofing) in SQL Server
  4. What are the main performance differences between varchar and nvarchar SQL Server data types?
  5. SQL Server String or binary data would be truncated
  6. Datatype for storing ip address in SQL Server
  7. How to change identity column values programmatically?
  8. best way to convert and validate a date string
  9. Why use the INCLUDE clause when creating an index?
  10. CAST and IsNumeric
  11. SQL Server pivot vs. multiple join
  12. SQL Query to find the last day of the month
  13. SQL Server – Dynamic PIVOT Table – SQL Injection
  14. SQL Server, convert a named instance to default instance?
  15. SQL Server GUID sort algorithm. Why?
  16. WHERE clause on SQL Server “Text” data type
  17. SQL Server error on update command – “A severe error occurred on the current command”
  18. Parse XML in SQL Server
  19. SQL server schema and default schema
  20. SQL Server ON DELETE Trigger
  21. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. FOR XML PATH(”): Escaping “special” characters
  24. How to store directory / hierarchy / tree structure in the database?
  25. INFORMATION_SCHEMA vs sysobjects
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. “Prevent saving changes that require the table to be re-created” negative effects
  28. Convert UTC Milliseconds to DATETIME in SQL server
  29. Table Valued Function Killing My Query Performance
  30. @@ERROR and/or TRY – CATCH

Leave a Comment