What do cursors do internally? I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR...

The client saw this SP ran for more than a day so, my manager assigned this to me.

Upon searching, I used table variable and a while loop to iterate each record.

To my surprise, the difference between the two was huge.

Someone told me that while loop is essentially, a CURSOR.

But when I used table variable with a WHILE LOOP, it ran very very very fast.

Now, what do cursors do internally?
Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose.

There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other resources are needed, could cause parts of the query to be rerun, and so forth. Also your inner loop may be doing things that affect the behaviour of the cursor by updating base tables it reads from in a way that makes the database engine need to do more work.

If we could see the two bits of code you are talking about we could be more specific.

By running the initial query into a table variable and looping through that you are essentially emulating a STATIC FAST_FORWARD READ_ONLY cursor, those options remove quite a chunk of the complexity that the engine might otherwise have to worry about. Your main SELECT is definitely run once and in one go, and your loop is not competing with other activity on the base data while it interacts with the copy in the variable.

Of course you might also find that something within your two loops (the one using the cursor and the one without) has significantly different performance metrics if they are not written exactly the same.