When does a sub-query become a correlated sub-query?

A correlated subquery refers to fields in the outer query, and therefore changes (potentially) for each row in the outer query.

A subquery that isn't correlated is self-contained and only needs to be run once.

Edited to provide an example:

The subquery here is non-correlated. It returns the same number every time.

SELECT *, (SELECT COUNT(*) FROM Production.Product AS p) AS NumProducts
FROM Production.ProductSubcategory AS s;

The subquery here is correlated, and will return the number of products in the relevant subcategory.

SELECT *, (SELECT COUNT(*) FROM Production.Product AS p 
           WHERE p.ProductSubcategoryID = s.ProductSubcategoryID) AS NumProducts
FROM Production.ProductSubcategory AS s;

One common mistake people often make with correlated subqueries is to provide that hook into the outer query (making it non-correlated). If you're do that with a WHERE EXISTS clause, you turn the WHERE EXISTS into an all-or-nothing predicate (rather than something which is evaluated separately, and thereby filtering only some rows).