How does SQL Server's midstream target handle data?

A cursor is a way of working with data, and in order to view or manipulate the data in the result set, the cursor provides the ability to move forward or backward through the data in the result set one time, either in rows or in multiple rows. We can use a cursor as a pointer, which can specify any location in the result, and then allow the user to manipulate the data at the specified location.

1. Composition of cursors

The cursor contains two parts: one is the cursor result set and one is the cursor position.

Cursor result set: Defines the collection of rows returned by the SELECT statement for the cursor. Cursor Position: The current pointer to a row of this result set.

2. Classification of cursors

There are 3 types of cursors:

1. API server Cursors
2. Transaction-sql Cursors
3. API client cursors.

The first two cursors are run on the server, so they are also called server cursors.

API server Cursors

API server cursors are primarily applied to the service, and the server processes the API functions when the client's application invokes the API cursor function. The following functions can be implemented using API functions and methods:

1. Open a connection.
2. Sets the attributes or properties that define the cursor characteristics, and the API automatically alludes cursors to each result set.
3. Executes one or more transaction-sql statements.
4. Extracts the rows in the result set using an API function or method.

API server cursors contain the following four types: static, dynamic, forward-only, keyset-driven (Primary key)

• The complete result set of a static cursor is stored in the staging table when the cursor is opened, (static cursors are always read-only). Static cursors have the following characteristics: Always display the result set as it is when the cursor is opened, do not reflect any modifications made in the database, and do not reflect changes made to the column values of the result set rows; the rows that are newly inserted in the database after the cursor is opened; the rows that make up the result set are updated by other users But a static cursor displays the rows that were deleted from the database after the cursor was opened.
• Dynamic Cursors In contrast to static cursors, dynamic cursors reflect all changes in the result set when the cursor is scrolled. The row data values, order, and members of the result set will change each time the member is fetched.
• A forward-only cursor does not support scrolling, it only supports fetching rows of data sequentially from beginning to end. Note: A forward-only cursor also reflects all changes made to the result set.
• Keyset-driven Cursors feature both static and dynamic cursors. When a cursor is opened, the members of the cursor and the order of the rows are fixed, and the keyset is stored on the temporary worksheet when the cursor is opened, and changes to the data values of the non-keyset columns are visible when the user cursor scrolls, and the rows inserted in the database after the cursor is opened are not visible unless the reopen cursor is closed.

Transaction-sql Cursors

The cursor is based on the DECLARE CURSOR syntax and is used primarily in transaction-sql scripts, stored procedures, and triggers. The Transaction-sql cursor processes the TRANSACTION-SQL statement sent by the client to the server on the server.

The process of using a TRANSACTION-SQL cursor in a stored procedure or trigger is:

1. Declares that the Transaction-sql variable contains the data returned by the cursor. Declare a variable for each result set column. Declare a variable that is large enough to hold the value returned by the column and declare the type of the variable to be the data type that can be implicitly converted from the data type.
2. Use the DECLARE CURSOR statement to associate the TRANSACTION-SQL cursor with the SELECT statement. You can also use the DECLARE cursor to define features such as read-only, forward-only, etc. of the cursor.
3. Use the Open statement to execute a SELECT statement to populate the cursor.
4. Use the FETCH into statement to extract a single row and move the data in each column to the specified variable. Note: Other Transaction-sql statements can refer to those variables to access the extracted data values. Transaction-sql cursors do not support extracting row blocks.
5. Use the close statement to end the use of the cursor. Note: After you close the cursor, the cursor is still present and can be opened using the Open command, and only the call to the DEALLOCATE statement will be released completely.

Client Cursors

The cursor caches the entire result set on the client using the default result set, and all cursor operations are performed in the client's cache. Note: client-side cursors only support forward-only and static cursors. No other cursors are supported.

Reprinted from: http://www.aspnetjia.com

How does SQL Server's midstream target handle data?