I had a small experiment to prove the point under discussion, I have a table accounts in SQL Server database, I added 20000 rows into the table with 9 columns and approximately 1 Kilobyte of data per record. After loading into my Business layer i am converting it back to a DTO, and returning to my web service. Just to test the performance I added two performance counters, one for “for each” loop and one for “for loop”, The results are obvious, foreach loop took 107 milliseconds to execute the same process, while classic for loop took 14 milliseconds.
Now why does this happen, lets get a bit deeper.
The for loop version uses enough stack space for only two local variables (counter and i). The Foreach version uses stack space for four locals (item, AccountList object, and two compiler-generated temporaries).
When a method is called in the CLR, all of the memory required for the locals is allocated upon the stack. As this takes place on the stack, this process is fast, but it is not free. That is why the foreach-loop will incur a small cost due to its extra two local variables.
There is something else, which changes the effect.
If we use the local variable multiple times in for loop and foreach loop, see what happens.
The game is reversed, that means that if we have to access the local variable value multiple times in the for loop, in that case the performance will decrease.
a- This for-each loop is faster because the local variable that stores the value of the element in the array is faster to access than an element in the array.
b- The for-loop is faster than the foreach-loop if the array must only be accessed once per iteration.
Sometimes when we import database tables from external server to our server, schema names are mixed up, like some tables show up as A.Table1 and other as B.Table2
We want that all should be A.table1 and A.Table2, below script can help in this:
DECLARE tabcurs CURSOR FOR
SELECT ‘B.’ + [name]
FROM sysobjects WHERE xtype = ‘u’
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname
WHILE @@fetch_status = 0 BEGIN
EXEC sp_changeobjectowner @tname, ‘dbo’
FETCH NEXT FROM tabcurs INTO @tname
In a relational dtaabse, operations are carried out on a complete set of rows in a table. The SELECT statement returns all the rows that satisfy the condition in the WHERE clause of the statement. This complete set of rows that the SELECT statement returns is called a resultset.
Some applications, such as interactive and online applications, cannot work effectively with the entire set as a unit. These applications need a mechanism to work with one row, or a small block of rows at a time.
For example, consider that you want to see a list of soaps available in a shop. In technical terms, the user wants selected records from the table. Once these records are available, you also would like to see all the records one by one.
A cursor is a database object that applications use to manipulate data by rows instead of recordsets. You can use cursors to perform multiple operations in a row-by-row manner, against the resultset. You can do this with or without returning to the original table. In other words, cursors conceptually return a resultset based on tables within a database.
You can use the cursors to do the following:
Allow positioning at specific rows of the resultset.
Retrieve a single row, or set of rows, from the current positions in the resultset.
Support data modifications to the rows at the current position in the resultset.
Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset.
Creating a Cursor
You can use the following two methods to create a cursor in SQL Server:
The T-SQL language, which supports the syntax for using cursors modelled after the Sql-92 cursor syntax.
Database application programming interface.
In this article, we will focus on T-SQl cursors. The syntax of T-SQL cursors and API cursors is different, but they follow a common sequence of steps.
Follow these steps to create a cursor:
Associate a cursor with a resultSet of a T-SQL statement, and define the characteristics of the cursor, such as how the rows are going to be retrieved, and so forth.
Execute the T-SQL statement to populate the cursor.
Retrieve the rows in the cursor. The operation to retrieve one row or a set of rows is called fetch. Scrolling is a series of fetch operations to retrieve the rows in a backward or forward direction.
You can also perform the modifications on a row at the cursor position.
Close the cursor.
Now We Shall See the Syntax
Use the DECLARE statement to create the cursor. It contains a SQL statement to include the records from the table.
DECLARE <Cursor_Name> CURSOR
After creating the cursor, you will open it. Use the OPEN statement to make the cursor accessible. OPEN <Cursor_name>Use the FETCH statement to obtain the records from the cursor for further processing. FETCH <Cursor_name>Use the CLOSE statement to temporarily close the cursor when it is not required. This statement releases the current resultset to close an open cursor. You have to re-open the cursor to fetch the rows. CLOSE <Cursor_Name>When you no longer require the cursor, you can use the DEALLOCATE statement to remove its reference. DEALLOCATE <Cursor_Name>Fetching and Scrolling Through a Cursor
When you open a cursor, the current row pointer in the cursor is logically before the first row. T-SQL cursors can fetch one row at a time. The options for FETCH operations are as follows:
FETCH FIRST Fetches the first row in the cursor
FETCH NEXT Fetches the row after the previously fetched row
FETCH PRIOR Fetches the row before the previously fetched row
FETCH LAST Fetches the last row in the cursor
FETCH ABSOLUTE n If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
FETCH RELATIVE n If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again.
By default, the FETCH NEXT option works. To use other options, you must include certain options in the DECLARE statement while creating the cursors.
DECLARE Statement Options
You can add the following attributes to the cursor to enhance its scrollability of a cursor. The attributes also are explained in the following list.
DECLARE <Cursor_Name> CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR <Selecting Statements>
[FOR UPDATE [OF Column_name[,….N]]]LOCAL: Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.
The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates.
The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.
GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.
FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor.
When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor).
If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.
DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.
FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
READ_ONLY: Prevents updates from being made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.
select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within a select_statement of a cursor declaration. SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.
UPDATE [OF column_name [,…n]] defines updatable columns within the cursor. If OF column_name [,…n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.
Declare Flight_cursor CURSOR SCROLL
FOR SELECT * from flight Order By Aircraft_code
Fetch First From Flight_Cursor
While @@Fetch_Status =0
Fetch NExt from Flight_Cursor
Explanation of Example 1
This simply creates a cursor that selects all records from a flight table, then it opens the cursor. Then, it fetches the first record from the cursor and it moves the cursor ahead until the last executed cursor FETCH statement returns 0.
returns an integer for the last executed cursor FETCH statement. @@CURSOR_ROWS returns the number of qualifying rows that are present in the currently open cursor.
Declare @@counter int
Declare @@ProductID int
Declare @@ProductName varchar(30)
Declare @@Qty int
Declare special cursor
local Scroll Keyset Optimistic
Select * from Products
Open special /* Opening the cursor */
fetch Absolute 1 from special
fetch next from special
/*set @@ProductName=(Select Prod_Name from special)*/
Update Products set Prod_Name= @@counter
Explanation of Example 2
In this cursor, we are fetching the values from the recordSet in set of variables. We further use them to update the record. CLOSING and DEALLOCATING is a good practice; it increase performance and security.