New thing to learn... 🙂
Whilst I agree, I wonder if anyone has ever used this idea in a real piece of coding? The values are hard-coded and that's something I try my hardest to avoid.
I'll have to reply with a bit of yes and a bit of no.
No, I have not (yet??) used a cross join between two hard-coded lists of constant in real work. Not saying it will never happen, though!
No, I have not used the exact syntax used in this question, but that is for backwards compatibility reasons only.
Yes, I have used a hardcoded source of rows in a query. The context was an "instead of" trigger. There was a requirement that the "previous" version of each row should also be in the table, but this was implement transparently to the front end. So the front end would just insert a row, and the instead of insert trigger would ensure that in reality, two versions of the row were inserted - the "current" and the "previous" version. At the heart of this trigger was a statement that went somewhat like this:
INSERT INTO TheTable(Version, Col1, Col2, ...)
SELECT x.Version, i.Col1, i.Col2, ...
FROM inserted AS i
SELECT 'previous') AS x(Version);
If I had to build this now, and I didn't have to provide support for SQL Server 2005 and older, I would replace the UNION ALL subquery with a VALUES clause as in this question.