为什么在SQL Server中使用游标被认为是不好的做法？ [英] Why is it considered bad practice to use cursors in SQL Server?

问题描述

我知道一些性能原因回到SQL 7天，但同样的问题仍然存在于SQL Server 2005中？如果我有一个结果集在存储过程中，我想单独采取行动，游标仍然是一个不好的选择？如果是，为什么？

I knew of some performance reasons back in the SQL 7 days, but do the same issues still exist in SQL Server 2005? If I have a resultset in a stored procedure that I want to act upon individually, are cursors still a bad choice? If so, why?

推荐答案

因为游标占用内存并创建锁。

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.

这篇关于为什么在SQL Server中使用游标被认为是不好的做法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！