Learn T-SQL Querying

Using cursors

Cursor usage must be kept to a minimum. Depending on the cursor type, these may leverage TempDB worktables, which is an I/O penalty. Because cursors operate in a row-by-row fashion, they force the database engine to repeatedly fetch a new row, negotiate blocking and manage locks, and output each row result individually.

Consider whether set-based logic can be used. In some cases, cursors appear more straightforward, but leveraging T-SQL constructs such as Common Table Expressions (CTEs) or temporary tables may achieve the same results with less overhead. If a set-based approach is not possible, most cursors can be avoided by using a WHILE loop, that is, if there is a primary key or unique key in the table. However, there are ...