Microsoft SQL Server ResourcesAvoid Using Cursors in SQL Server

Provided by: Molly Pell, Technical Project Manager

Transact SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts where they make the contents of a result set available to other Transact SQL statements. A cursor may contain one, some, or all records from an executed SQL statement.

Cursors generally use a lot of resources and reduce the performance and scalability of applications. If you need to perform row-by-row operations, try to find another method to perform the task. For example, consider the following options:

  1. Perform the task at the client
  2. Use tempdb tables
  3. Use derived tables
  4. Use a correlated sub-query
  5. Use the CASE statement

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor.

If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors, in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS