The scheme of programming in T-SQL is relational (not procedural and not OOP) and this language has his own rules and due to will be necessary try to avoid the next common practices:
Cursors or WHILE: DO NOT USE
The cursors are extremely danger for the performance of our solution, the logic to apply when we use a cursor is that we need to process one row at the each time, but in the reality almost never we don’t need this.
In this example we will use a loop logic (the same that apply a cursor):
DECLARE @Counter INT,@End INT=100000 SELECT @Counter = 0 WHILE @Counter <= @End BEGIN SELECT @Counter SET @Counter=@Counter+1 END
Previous code make a loop where print a range of values from 1 to 100000. The impact in the Server was:
In place of this code we will use another function optimized for set logic (http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers) :
WITH L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5) SELECT n FROM Nums WHERE n <= 100000;
In short words, always try to do the solution based on SET THEORY and you must forget try the loops and classic structure programming or OOP on T-SQL and remember: Rows are not Records and Fields are not Columns 🙂 . Good Code !!!