|
Leverage SQL Server's Cursorless Mode
Improve your code by understanding how SQL Server's default resultset works.
by Bob Beauchemin
Posted October 7, 2003
Technology Toolbox: C#, SQL Server 2000
The ADO.NET SqlClient data provider never uses a server cursor to access your data, whether you use SqlDataReader or SqlDataAdapter. Instead, it uses a special forward-only, read-only cursorless mode (also called the "default resultset" in SQL Server Books Online). I'll discuss using server cursors from the client and explore cursorless mode's behavior in detail. I'll answer the question: "What actually happens at the client and—more important—at the server when you execute a SqlCommand that returns a resultset from the client?" Understanding how cursorless mode works allows you to use the provider more effectively and avoid coding techniques that could lead to errors (download the sample code).
ADO.NET handles resultsets differently from classic ADO. Although ADO.NET's and ADO's default behavior is the same, the same Recordset.Open statement in classic ADO exposes three possible behaviors: You can use SQL Server's cursorless mode (the default); you can use four types of server-side cursor; or you can tell the provider to use cursorless mode to fetch all the data back to the client (see Listing 1). The third option is called a client-side cursor. (Some parts of the SQL Server documentation—and some authors—refer to SQL Server's cursorless mode as a fast-forward cursor. Although SQL Server has a fast-forward cursor that has some of cursorless mode's behaviors, it's not quite the same thing, as I'll explain later.)
The difference between a client-side cursor and a server-side cursor in classic ADO is substantial and can be confusing. A server-side cursor allows you to manipulate rows on the server through calls on the client, usually storing all or a portion of the data in TEMPDB. The client-side cursor fetches data into a COM object on the client. Its name comes from the fact that the buffered data on the client exhibits cursor-like behaviors—you can scroll through and, potentially, update it. The behavior difference manifests itself in a few ways. Fetching a large resultset into a client cursor causes a big performance hit on the initial fetch, and server cursors result in increased memory requirements for SQL Server and require a dedicated connection all the time you're fetching.
Back to top
|