Actually, you give me too much credit - I didn't even consider that for method 1 (not sure whether that was because I was counting it as unreliable anyway or because I didn't think of it - it's a while since I submitted the question).

I did consider it for method 4, because I think I read somewhere that CTEs generally work by text substitution, so that if the same CTE is referred to more than once in a query the two instances may deliver different values. Of course the optimizer may decide to common up textually equal expressions, provided that doing so doesn't screw up store management, but I don't know whether it would do that or not; and of course recomputing the content of a CTE could lead to some new variations of queries whose semantics could be altered according to whether intermediate results are spooled or not (so if it's an update query, new instances of the Harlequin Problem - of course according to some people where insert and delete queries have their semantics modified in a similar manner that would be the Harlequin Problem too) so it's sometimes essential to spool a CTE. Spooling every CTE would be a performance nightmare, so that presumably doesn't happen. All that leaves me non the wiser about whether this particular query will have a problem, so I decided to test method 4 fairly thoroughly. On my machine, with a reasonable amount of background work going on, I invoked this method 10000 times in a loop to write a row into a table with the time and the nanoseconds since midnight (the final select of method 4 was replace by

insert #pig select (1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)) , t

from secs cross join dy cross join secstart cross join noww;

to do this - for some reason in my notes for the test all occurrences of now have two ws, presumably I wanted to avoid the reserved [???] word) and then counting the number of rows where the nsec column was the same as the nanosec count calculated by method 2 from the t column), and also counting the number of rows where it was different, delivered counts of 10000 for same, and 0 for different. This was repeated at several times of day and with several different background workloads added (AV and spyware scan, database backups, intense numeric stuff, massive automatic text edits, indexing files - , network dowloads, varios combinations of these) with the same results.

What that tells me is that on my machine either the optimiser decides to calculate that CTE just once, or the time difference between the two times it calculates it is always less that 100NS, and I think the latter implies that the query (up to the point of having the output ready to write to the log) takes less time than the minimum interval (if there is such a thing in this verson of NT) that a thread is given when selected before it will be interupted. So for me method 4 is reliable. The scary thing is that I have no real proof that it is actually reliable on any other machine, or that it will be reliable in SQLK 2012 (I'm using 2008 R2), or that it will be reliable if I substitute some version of Windows 7 for Windows XP Pro. Despite that, I'm sure that it is indeed reliable, at least in SQL 2008 R2.

Of course this risk can be eliminated completely by having the query work off a variable previously initialised to sysdatetime(), and that applies equally to method 1 (for the few hours each day that it works), so these methods can be made safe (for method 1, safe for a few hours per day) by converting them into two statements each instead of 1. But of course I prefer method 2 to method 4, because it's simpler and even in its single statement form doesn't carry this risk.

edit: Why avoid "now" as a reserved word? It isn't a reserved word in T-SQL. I must have been thinking of some other language (maybe ISO SQL - it has more reserved words than T-SQL - but I don't think so).