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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- declare variables used in cursor DECLARE @city_name VARCHAR(128); DECLARE @country_name VARCHAR(128); DECLARE @city_id INT; -- declare cursor DECLARE cursor_city_country CURSOR FOR SELECT city.id, TRIM(city.city_name), TRIM(country.country_name) FROM city INNER JOIN country ON city.country_id = country.id; -- open cursor OPEN cursor_city_country; -- loop through a cursor FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name; WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONCAT('city id: ', @city_id, ' / city name: ', @city_name, ' / country name: ', @country_name); FETCH NEXT FROM cursor_city_country INTO @city_id, @city_name, @country_name; END; -- close and deallocate cursor CLOSE cursor_city_country; DEALLOCATE cursor_city_country; |
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:
- How to query the information schema database
- How to combine a few commands/statements we’ve mentioned in previous articles (IF … ELSE, WHILE loop, CONCAT)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
-- declare variables used in cursor DECLARE @table_name VARCHAR(128); DECLARE @table_names_5 VARCHAR(128); -- declare cursor DECLARE cursor_table_names CURSOR FOR SELECT TOP 5 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME ASC; SET @table_names_5 = 'first 5 tables are: ' -- open cursor OPEN cursor_table_names; -- loop through a cursor FETCH NEXT FROM cursor_table_names INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN IF @table_names_5 = 'first 5 tables are: ' SET @table_names_5 = CONCAT(@table_names_5, @table_name) ELSE SET @table_names_5 = CONCAT(@table_names_5, ', ', @table_name); FETCH NEXT FROM cursor_table_names INTO @table_name; END; PRINT @table_names_5; -- close and deallocate the cursor CLOSE cursor_table_names; DEALLOCATE cursor_table_names; |
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
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
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020