Avoid correlated subqueries.

This query:

SELECT
p.Name AS ProductName
, (SELECT pc.Name
FROM Production.ProdutCategory AS pc
WHERE pc.ProductCategoryID = p.ProductSubcategoryID
) AS ModelName
FROM
Prodution.Product AS p;

May not be equvilent to this query:

SELECT
p.Name AS ProducctName
, pc.Name AS ModelName
FROM
Prodution.Product AS p
LEFT JOIN Prodution.ProductCategory AS pc — Left inner join from Product to ProductCategory
ON pc.ProductCategoryID = p.ProductSubcategoryID;

The first query could have NULL values returned by the subquery. This would not be
the case it there was a foreign key relationship between Product and ProductCategory
where the values in ProductSubcategoryID must exist in ProductCategoryID.

If NULL values are possible, then the new query would need to look like this:

SELECT
p.Name AS ProducctName
, pc.Name AS ModelName
FROM
Prodution.Product AS p
LEFT OUTER JOIN Prodution.ProductCategory AS pc
ON pc.ProductCategoryID = p.ProductSubcategoryID;

Use small batcches in delete an upddate statements.

The original script in the article will work when being run manually in SSMS.
However, if you are writing a procedure, the code provided won’t work.
You will need something like this:

DECLARE @BatchSize int
, @LoopSize int
, @LoopCnt int;

SET @BatchSize = 1000;
SET @LoopSize = CEILING((1.0 * (SELECT COUNT(*)
FROM Sales.SalesOrderDetail AS sod
WHERE sod.CarrierTrackingNumber IS NULL
)
) / @BatchSize
);
SET @LoopCnt = 0;
WHILE @LoopCnt < @LoopSize
BEGIN
BEGIN TRAN
UPDATE TOP(@BatchSize) Sales.SalesOrderDetail SET
CarrierTrackingNumber = 'NewNumber_01'
WHERE CarrierTrackingNumber IS NULL;
COMMIT TRAN;
SET @LoopCnt += 1;
END;