Cursors are a way of manipulating data and interacting with them one at a time. They have a bad reputation within the SQL world as they go against the SET based logic and they can have a very high performance cost. Where possible you should ask yourself whether you could avoid using a Cursor. This is because of the performance advantages a SET based solution has and that Cursor problems only increase when the tasks are scaled up.
The five general steps of a cursor are:
- Declaration of the cursor
- Opening the cursor
- Fetching and manipulating the data
- Closing the cursor
- Deallocating the cursor
A simple example of a Cursor is below.
Declare @Databases varchar(50)</pre>
Declare DatabasesOnIntance CURSOR READ_ONLY FOR SELECT name FROM sys.databases order by name
Open DatabasesOnIntance
Fetch next from DatabasesOnIntance into @Databases
While @@FETCH_STATUS = 0
Begin
Print @Databases
Fetch next from DatabasesOnIntance into @Databases
End
Close DatabasesOnIntance
Deallocate DatabasesOnIntance
As you can see the Cursor I have created has printed each database on the instance
Originally published at https://parvtheitgeek.com on March 23, 2015.
Parv The IT Geek 