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