T-SQL Tips and Tricks

T-SQL Tips and Tricks

Cursors—are ok if you are just iterating through, but are bad for updating. Instead do Select Top 1 and Order By to update a temp table. It is the best method if you need to do anything to the data.

Common Table Expressions – are best for solving complex problems. Non-persisting temp table. Can be used in places where a temp table doesn’t work.

With CTE_TechEd
AS
(
Select * from Sales.SalesTerritory
)
Select * from CTE_TechEd

Recursive Query with MAXRECURSION

With SimpleRecursive(field, field)
As
(
Select Emp, Emp_Id, Reports_To_Employee from Employee_Tree where Empl_id = 2
UNION ALL
Select Emp, Emp_Id, Reports_To_Employee from Employee_Tree P Inner Join
simpleRecursive A on Empl_id = P.Reports_to_Employee
)
Select sr.Emp_ID as employee, et.Emp as Boss
From SimpleRecursive sr Inner Join on sr.Reports_To = et.empl_id
--Employee_Tree et Option(MAXRECURSION 2)
(Note: this is supposed to stop recursion at whatever level (number of times) you set,
However, a level 16 error gets raised in ADO.Net error 530)

Common Table Expressions method of finding dupes rather than self-join
Finding and removing duplicates with a CTE