Try to avoid – Tips for finding the road to a better TSQL code (Part I)

Try to avoid – Tips for finding the road to a better TSQL code (Part I)

Try to avoid – Tips for finding the road to a better TSQL code (Part I)

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 !!!

 

Leave a Reply

Your email address will not be published. Required fields are marked *