For example CHECKSUM(*) is nondeterministic, despite the fact that it will always return the same value whenever called unless the state of the database has changed between calls...
That depends. Do you consider a non-persisted calculated column a change to the state of the database? Try this:
CREATE TABLE #t1 (ID INT, DT AS GETDATE());
INSERT [#t1] ([ID]) VALUES (1), (2), (3)
Then run this a few times:
SELECT ID, CHECKSUM(*) FROM #t1
I see your point. But I'm not claiming that CHECKSUM() is always deterministic with argument '*', only that it's deterministic when all the columns in the list, whether explicit or implied by '*', are themselves deterministic.
The definition could be cleaned up a bit to make sense: reference to a column which is non-persisted and computed by reference to a nondeterministic function should make a call of CHECKSUM which refers to that column indeterministic (for all I know it already does when the column is named in the argument list); and checksum(*) should be indeterministic when the table has such a column, but not otherwise. The fact that it could change to have such a column in the future is neither here nor there, it's a database state change so by definition it doesn't impact determinism (it would be a nuisance to code round, of course, if it's a column in an indexed view - but it can't be, as * can't be used in a schema bound object). If DT were a deterministic column then CHECKSUM(ID, DT) would be deterministic but a change in database state could make it nondeterministic. Of course such a change can't happen if the column is involved in a schema bound view definition, and the correct solution for * would be the same: don't allow the meaning of * to change if a view is dependent on it. And since * is not allowed in schema-bound views, why claim it makes CHECKSUM non-deterministic? Has anyone observed a case where CHECKSUM(*) changes its result but CHECKSUM(<list of every column in the table>) doesn't? I certainly haven't, in fact I can't imagine there being any such case, and I suspect that what has happened is that whoever wrote the BOL page that claims CHECKSUM(*) is indeterministic is labouring under the delusion that "indeterministic" means "forbidden in <list of contexts>", ie it's a statement based on the perverted view of indeterminism that I complained about.