Introduction
The cursor is one of the important elements in the SQL Server. As you know, it will do the row-by-row operation. It will affect the performance of the SQL Server because it will use a lot of IO to do the operations. The web application performance not only depends on the application. We have to consider the database also. Because when we want to display the records from the database, then it must effectively do the backend processing. Let us see with an example.
Cursors in SQL Server
The cursors will be used when we want to perform the looping of the table result set. In the SQL server, we don't have any inbuilt functions to do the looping of the Table results like Array in any programming language. If you want to iterate the table based on the key, then we cannot say it will be in the sequence order.
Consider an example; I want to perform the product sales report for every product in the Product Master table. If the product key is an integer, then we don't have any problem in such cases. If it is a non-integer, then definitely we cannot iterate.
Suppose you have 10 products in the product table in the order of Product ID is 1,2,3,4,5,6,7,8,9,10. Then we don't have any problem. But two products have been deleted, then now it's ordered like 1,3,5,6,7,8,9,10. Then there is something that needs to perform cursor. Just I take the Product table as an example to catch easily.
The cursor what it does, it will be stored as an object in the memory, then it has the inbuilt row cursor, then it will do one by one. Then it will allocate some memory space in the SQL server memory; it must be deallocated and closed its references in the memory.
The first procedure has been implemented using the simple cursor. It will do a row-by-row operation using the cursor. It will fetch the record from the cursor result. As we discussed, it will take a lot of IO reads, ad it lies a lot of memory. It will affect the performance of the other query processing in the SQL server.
The second procedure shows an alternative way to implement the cursor. It will give the same result as stored procedure 1. Here it's like ordinary SQL statements using the table variable.
As I said, when we need the cursor. If you cannot get an order of sequence to process the table, then we must need something like a cursor to process row-by-row processing.
Just think we need an order of sequence. For that, we have created the table variable; what it. Does it have one identity column and key field, which we are going to process the other functionalities?
Here consider the Product ID like this.
1, 3,5,7,8,9,10
So definitely, I cannot take the product id in the loop to process.
CREATE @ProdTable TABLE (
iSNo INT IDENTITY(1,1),
ProductID INT
)
INSERT INTO @ProdTable(ProductID)
SELECT ProductID FROM Products
SELECT * FROM @ProdTable
| iSNo |
ProductID |
| 1 |
1 |
| 2 |
3 |
| 3 |
5 |
| 4 |
7 |
| 5 |
8 |
| 6 |
9 |
| 7 |
10 |
Now we have the order of sequence in the table. Then we can perform the row-by-row operations using the looping statements in the SQL server.
Here the entire product id will be inserted into the table variable. Here we have used the table variable. It will give more performance. The same result can be achieved using the temp table. As you know, the temp table will be stored in the TempDB database. It will work across the database. It will occupy the unnecessary spaces in the SQL server memory. It must be deleted once the process gets over.
But the table variable is best when we are going to use limited records in the table. Because it will be there in processor memory, once it crosses the level of the data storage, then it will move to the tempdb. This lifetime of the table is only up to that stored procedure.
Conclusion
I hope that this above method will be very useful. I expect your valuable suggestions and corrections. I have attached this script exercise in this article.