Emil Drkusic
SQL Server Cursor - the data model

Learn SQL: SQL Server Cursors

June 22, 2020 by

SQL Server cursors are one common topic on the Internet. You’ll find different opinions when to use them and when not to do it. Today, we’ll also talk about them and answer the question when (not) to use them.

The Data Model and the general idea

In the previous article, Intro to SQL Server loops, we talked about SQL Server loops, but we haven’t used data from the database. That was odd, but that should become much clearer now. Today, while explaining cursors, we’ll use the data from the database to show when (not) to use cursors. The data model we’ll be using is the same one we’re using throughout this series.

SQL Server Cursor - the data model

SQL Server supports 3 different implementations of cursors – Transact-SQL cursors, API cursors, and Client cursors. In this article, we’ll focus on Transact-SQL cursors. You’ll easily recognize them because they are based on the DECLARE CURSOR syntax.

SQL Server Cursor – Introduction

Before we move to code and examples, we should explain what SQL Server cursors are.

The SQL Server cursor is T-SQL logic, which allows us to loop through the related query result. This enables us to take the actions sequentially – e.g., perform an update on a single row.

Sometimes this could (seem to) be helpful, but when working with databases, you shouldn’t use procedural programming patterns but rather stick to declarative programming. One of the main reasons is that DBMSs are already optimized to perform actions on sets of data, and therefore you shouldn’t be the one who’s trying to be “smarter than the system”.

Still, it’s good to know how they work. If nothing else, maybe you’ll meet them in the code you inherit, and you’ll have to rewrite the logic. And before doing anything, you should understand how it works.

So, in case you need cursors, this is what you should know about them:

  • Cursors use variables to store values returned in each part of the loop. Therefore, you’ll need to DECLARE all variables you’ll need
  • The next thing to do is to DECLARE … CURSOR FOR SELECT query, where you’ll declare a cursor and also define the query related to (populating) that cursor
  • You’ll OPEN the cursor and FETCH NEXT from the cursor
  • In the WHILE loop you’ll test the @@FETCH_STATUS variable (WHILE @@FETCH_STATUS = 0). If the condition holds, you’ll enter the loop BEGIN … END block and perform statements inside that block
  • After you’ve looped through the whole result set, you’ll exit from the loop. You should CLOSE the cursor and DEALLOCATE it. Deallocating is important because this shall delete the cursor definition and free the memory used

SQL Server Cursor – Examples

Let’s now take a look at two cursor examples. While they are pretty simple, they nicely explain how cursors work.

In the first example, we want to get all cities ids and names, together with their related country names. We’ll use the PRINT command to print combinations in each pass of the loop.

using SQL cursor example

Using the SQL Server cursor and the while loop returned exactly what we’ve expected – ids and names of all cities, and related countries, we have in the database.

The most important thing to mention here is that we could simply return this result set using the original SQL query stored in the DECLARE part of the cursor, so there was no need for a cursor.

We’ll go with one more example. This time we’ll query the information schema database to return the first 5 tables ordered by table name. While there’s not much sense in using such a query, this example shows you:

SQL Server Cursor - example with the information schema database

From the coding side, I would like to emphasize that this time, we haven’t printed anything in a loop but rather created a string using CONCAT. Also, we’ve used the IF statement to test if we’re in the first pass, and if so, we haven’t added “,”. Otherwise, we would add “,” to the string.

After the loop, we’ve printed the result string, closed and deallocated the cursor.

We could achieve this using the STRING_AGG function. This one is available starting from the SQL Server 2017 and is the equivalent of MySQL GROUP_CONCAT function.

SQL Server Cursor – When (Not) to use them?

I’ll try to give an objective answer to the question – “When you should use SQL Server cursors and when not”? Since things change during the time and improvements shall be made, either on cursors, either on other objects that “replace” them, take into consideration the date when this article was written. So, let’ start.

You shouldn’t use cursors:

  • Almost always 🙂 This might sound stupid, but that’s true in most cases. SQL Server implements a large number of objects & functions that do exactly what you would probably try to solve using cursors. Before deciding to go with the cursor, be sure you’ve investigated enough to conclude that the cursor is the only possible (good) solution. Same stands for loops in databases. In the previous article, Intro to SQL Server loops, we’ve used loops, but not to loop through data.

You could use cursors:

  • Mostly for database administration tasks like backups, integrity checks, rebuilding indexes
  • For one-time tasks when you’re sure that possible poor performance won’t impact the overall system performance
  • Calling a stored procedure a few times using different parameters. In that case, you would get parameters from cursor variables and make calls inside the loop

    Calling a stored procedure or another query inside the cursor (or loop) impacts performance a lot, because, in each step of the cursor loop, you’ll run the query/procedure from the start. If you decide to do that, you should be aware of possible consequences.

  • The previous hint brings us to the last bullet when you should use cursors. If you’re completely aware of how they work and you’re pretty sure it won’t impact performance, go for it

SQL Server Cursor – Why people (don’t) use them?

The last question I would like to answer is: Why would anyone use a cursor? This is how I see it:

  • People who’re using them for one-time jobs or regular actions where they won’t impact performance have the excuse. One of the reasons is that such code is procedural code, and if you’re used to it, it’s very readable
  • On the other hand, those who started learning about databases, and are used to procedural programming might use cursors because, as mentioned, they are much closer to procedural programming than to databases. This is not a reason to use them, because the only excuse here would be that you simply don’t know the other (right) way how to get things done
  • The most important thing about cursors is that they are slow when compared to SQL statements, and therefore you should avoid using them because they will sooner or later lead to performance issues (unless you know exactly what you’re doing and why)

I find it useful that you understand the concept of cursors because there is a great chance, you’ll meet them along the way. They were popular before some new options were added to SQL Server. Also, there is a chance you’ll continue working on a system where somebody before you used them, and you’ll have to continue where they stopped. Maybe you’ll need to replace the cursor (procedural code) with SQL (declarative code).

Conclusion

There is no better conclusion on cursors, than – don’t use them 🙂 SQL Server implemented a lot of changes that solve problems that were hard to solve using declarative code before. Better spend some time investigating and learning something new, and finally, producing optimal code. Of course, you can use them if you know why you are doing that, and you’re aware of possible problems related to them.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection

Emil Drkusic
Latest posts by Emil Drkusic (see all)
Cursors, SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

5,001 Views