Write SQL for the version you actually have

A recurring shape of problem in enterprise work: the elegant solution exists, is well documented, and is unavailable. The database in front of you is a version behind — sometimes several — and upgrading it is a project with a budget and a committee, not something you do to unblock a Tuesday.

Three that come up constantly on SQL Server 2014.

STRING_AGG does not exist

Concatenating a grouped set of values into one string is a one-liner from 2017 onward. Before that, the idiom is `FOR XML PATH`, and it needs care because XML encoding will mangle ampersands and angle brackets if you let it:

SELECT s.SubjectCode,
       STUFF((
           SELECT ', ' + q.QuestionLabel
           FROM   Questions q
           WHERE  q.SubjectCode = s.SubjectCode
           ORDER BY q.QuestionIndex
           FOR XML PATH(''), TYPE
       ).value('.', 'nvarchar(max)'), 1, 2, '') AS Questions
FROM   Subjects s
GROUP  BY s.SubjectCode;

The `, TYPE` and `.value()` pair is what protects you from the encoding problem. The `STUFF` removes the leading separator. It is uglier than `STRING_AGG` and it performs perfectly well.

TRY_CONVERT may not be available to you

Where `TRY_CONVERT` is not an option, you cannot rely on a safe cast, which matters enormously when the source column is `varchar` and holds values entered by humans over a decade. Validate explicitly instead:

-- ISNUMERIC is too permissive: it accepts '1e5', '+', '$1'.
-- Test for the shape you actually want.
SELECT CASE
         WHEN LTRIM(RTRIM(RawValue)) NOT LIKE '%[^0-9]%'
              AND LEN(LTRIM(RTRIM(RawValue))) BETWEEN 1 AND 18
         THEN CAST(LTRIM(RTRIM(RawValue)) AS bigint)
       END AS SafeValue
FROM   StagingTable;

The double-negative `NOT LIKE '%[^0-9]%'` reads awkwardly and means "contains no non-digit character", which is usually what you want and what `ISNUMERIC` will not give you. `ISNUMERIC` returns 1 for `+`, for `$1`, and for `1e5` — all of which then fail to cast to `bigint`, at row four hundred thousand of a migration.

ORDER BY inside a view does not do what you think

A view with `ORDER BY` and `TOP 100 PERCENT` appears to work and is not guaranteed to. The optimiser is free to ignore the ordering. Order in the query that selects from the view, not in the view itself. If ordering genuinely belongs to the object, it is a stored procedure or a table-valued function, not a view.

Why this is worth writing down

It is tempting to treat legacy version constraints as a nuisance to be complained about until the upgrade lands. In practice the upgrade is often years away and the business needs the report this week. Engineering around the version you have — deliberately, with the workaround documented and the reason recorded — is ordinary professional work, not a compromise.

The best solution that will not run on the server in front of you is not a solution.

One habit worth adopting: leave a comment at each workaround naming the version constraint that caused it. When the upgrade does happen, whoever is modernising the codebase can find every one of them with a single search instead of guessing.