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

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.

The above comments about SQL being a set-based environment are all true. However there are times when row-by-row operations are useful. Consider a combination of metadata and dynamic-sql.

As a very simple example, say I have 100+ records in a table that define the names of tables that I want to copy/truncate/whatever. Which is best? Hardcoding the SQL to do what I need to? Or iterate through this resultset and use dynamic-SQL (sp_executesql) to perform the operations?

There is no way to achieve the above objective using set-based SQL.

So, to use cursors or a while loop (pseudo-cursors)?

SQL Cursors are fine as long as you use the correct options:

INSENSITIVE will make a temporary copy of your result set (saving you from having to do this yourself for your pseudo-cursor).

READ_ONLY will make sure no locks are held on the underlying result set. Changes in the underlying result set will be reflected in subsequent fetches (same as if getting TOP 1 from your pseudo-cursor).

FAST_FORWARD will create an optimised forward-only, read-only cursor.

Read about the available options before ruling all cursors as evil.

There is a work around about cursors that I use every time I need one.

I create a table variable with an identity column in it.

insert all the data i need to work with in it.

Then make a while block with a counter variable and select the data I want from the table variable with a select statement where the identity column matches the counter.

This way i dont lock anything and use alot less memory and its safe, i will not lose anything with a memory corruption or something like that.

And the block code is easy to see and handle.

This is a simple example:

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))DECLARE @COUNT INT,        @MAX INT,         @CONCAT VARCHAR(MAX),         @COLUMN1 VARCHAR(10),         @COLUMN2 VARCHAR(10)SET @COUNT = 1INSERT INTO @TAB VALUES('TE1S', 'TE21')INSERT INTO @TAB VALUES('TE1S', 'TE22')INSERT INTO @TAB VALUES('TE1S', 'TE23')INSERT INTO @TAB VALUES('TE1S', 'TE24')INSERT INTO @TAB VALUES('TE1S', 'TE25')SELECT @MAX = @@IDENTITYWHILE @COUNT <= @MAX BEGIN    SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT    IF @CONCAT IS NULL BEGIN        SET @CONCAT = ''     END ELSE BEGIN         SET @CONCAT = @CONCAT + ','     END    SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2    SET @COUNT = @COUNT + 1ENDSELECT @CONCAT