Loop all records
Published on: 18th Oct 2019
Updated on: 19th May 2021
Overview
Cursor requires record locking the live data and it might not work as fast as you think. Especially if you have a busy database. The alternative way is to use a temporary working table which is easier to work with.
In this article, we are going to use a local temporary working table to keep the data and then loop through all records.
Here's how we achieve looping without using cursor
The following example prints all table names in the current database.
set nocount on
declare
@seq int
, @name nvarchar(255)
-- declare a temporary table in the memory.
create table #tb (
seq int identity(1,1)
, name nvarchar(255)
)
--copy all the necessary records to the temporary table '#tb'
insert into #tb (name)
select name
from sys.objects
where
type = 'u'
order by name
-- init the var before use
set @seq = 0
-- start the loop
while exists(
select *
from #tb
where seq > @seq
)
begin
-- always load the first record
select top 1 --<<=== this is very important!!!
@seq = seq
, @name = name
from #tb
where seq > @seq
order by seq --<<=== this is very important!!!
-- do whatever necessary process here.
-- you may use 'set xxx' to join the sql string before printing it out.
print '@name => ' + @name
end
drop table #tb
Basically, this is how it works
-
It declared a local temporary table call
#tb
. The most important field isseq
which is an auto-increment field. That means, inserting a new record into this table, SQL server will assign a new unique running number for that record. -
We must initialize
@seq
with the value of0
. Otherwise, theWHILE
loop will not start. -
The WHILE loop starts and checks if there is any record that is greater than
@seq
value. If yes, it will retrieve the first record that is greater than@seq
. -
Then, it does some data operations.
-
Upon completely looping through all records, it drops the local temporary table.
Table variable VS Local temporary table
From our experience, for the number of records that is less than 100, we prefer using table variables. Otherwise, we use a local temporary table. The reason is due to the performance.
-
Table variable
The table variable declaration looks like this,
declare @tb table ( seq int identity(1,1) , name nvarchar(255) )
- The benefit of table variables is that you don't have to drop table at the end of the stored procedure.
- It stores in
tempdb
. - It does not allow creating any index on the table variable.
- It does not have any record locking mechanism because the table variable is not able to share with any other stored procedure (it is accessible within the stored procedure who created it).
- If a table variable was declared outside of a dynamic query (where the query to be executed with
sp_executesql
), the table variable is not accessed by the dynamic query. - The table variable declared outside of the current stored procedure is not accessible.
-
Local temporary table
The local temporary declaration looks like this,
create table #tb ( seq int identity(1,1) , name nvarchar(255) )
-
It behaves the same as a normal table.
-
It stores in
tempdb
instead of the user database. -
You may create an index on the table.
-
You may declare a local temporary table and then access it with the dynamic query.
-
If a local temporary table has been declared in a stored procedure, it will be accessible by another stored procedure in the same session.
For example, stored procedure 1 creates a local temporary table. Then, it calls stored procedure 2 to insert some records. This is allowed.
-
SQL development VS C# development
SQL server is able to process the data fast and it will still be slow when you try to loop the record. As a developer, we should not always use the C# development concept to solve the data issue. Avoid looping unless there is no other choice.
When not to use loop
-
For example, generating a series of numbers.
In C#, we generate a series of numbers using the
FOR
loop.List<int> my_list = new List<int>(); for (int i = 0; i < 10; i++) { my_list.Add(i); }
In SQL, we may use CTE (Common Table Expression) to avoid explicit loop,
; with tb (seq) as ( select seq = 1 union all select seq + 1 from tb where seq < 10 ) select * from tb
-
Another example is patching data or process data does not require locking many resources.
In C#, we generate a series of numbers using the
FOR
loop.// load the overdue invoice DataTable tb = getOverdueInvoice(db_connection); String bill_no = null; decimal overdue_charges = 0; // loop through all records foreach (DataRow row in tb.Rows) { if (bill_no == null) { bill_no = row["bill_no"].toString(); overdue_charges = 0; } else if (bill_no != row["bill_no"].toString()) { // (insert the total into tb_overdue_charges table) insertOverdueCharges(bill_no, overdue_charges); bill_no = row["bill_no"].toString(); overdue_charges = 0; } // calculating the charges overdue_charges += (Decimal.Parse(row["qty"]) + Decimal.Parse(row["price"])) * 0.15; } // (insert the last total into tb_overdue_charges table) insertOverdueCharges(bill_no, overdue_charges);
In SQL, you can avoid using explicit loop when calculating charges on the overdue invoice,
insert into tb_overdue_charges ( bill_no, overdue_charges ) select bill_no , overdue_charges = sum(qty * price * 0.15) from tb_sales where datediff(day, trans_date, getdate()) >= 30 group by bill_no
When is the appropriate time to use loop
-
For example, calculating the overdue charges involves millions of records. In this case, you may have to split the process into smaller batches and process it multiple times. Please refer to the following articles for the explanations.
Tracking the progress of a long run process Design a stoppable long running process
-
Another example is generating user reminders which involves complex requirements. Please refer to the following articles for the explanations.
Jump to #MSSQL blog
Author
Lau Hon Wan, software developer.