The official and final texts are only available by purchase.
The drafts, otoh, are often no more than a google search away. Google for SQL200n.zip and you should get pretty close.
You have a few different questions in here.
Q: What are ANSI standard SQL functions?
ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that's one heck of a dry, boring read.
Q: Do ANSI standard SQL functions change data in the database?
No. You can use them to change data - for example, I can say:
INSERT INTO dbo.MyReport SELECT MAX(SalespersonRevenue) FROM dbo.Sales
But by themselves, just the use of a AVG, COUNT, MIN, MAX, etc shouldn't change data permanently inside your database.
Q: Does the ANSI standard allow me to write my own functions?
Yes, but the exact implementation varies from vendor to vendor. The functions you write may conform to the ANSI language standard, but what you do inside your function can be horrifically awful, like creating side effects.
- When discussing intended behavior, it's possible to get a cross-platform answer.
- When discussing side effects, it is not.
Q: Can I create my own function to write data?
Why sure, if you're creative. I'm a Microsoft SQL Server guy, so I'm going to focus on that platform. Books Online's function page says:
User-defined functions cannot be used to perform actions that modify the database state.
To which I say:
You're not my real dad.
So here's how I'd break the rules. Warning: very bad ideas follow.
- In your function, query a new table specially created for this evil purpose, and then create something that watches the table for select statements, and then fires an action (Extended Events, auditing, or a Profiler trace). You can hook together a Rube Goldberg sort of contraption to perform work based on those select statements.
- In the function, call CLR code - heck, you can even call a web service. That web service could very well push data back into your own database.
- In the function, call xp_cmdshell and do something through the command prompt. (HT @AaronBertrand in the comments.)
All of these examples have huge drawbacks in the form of performance and transactional consistency. You just asked if it could theoretically be done, and the answer there is yes. I wouldn't ever use either of those in my own code - I'd step back and ask, "What's the business goal I'm trying to achieve here, and is there a way I can do it to achieve performance and transactional consistency?" If you'd like specific advice on those, I'd ask a separate Stack question with specifics.
Best Answer
I think it's an implementation detail.
I think an implementer could evaluate a common table expression 20 times, even in 20 different ways, and still have a conforming implementation. The only relevant issue is whether "its effect . . . is identical to the effect" of the sequence of actions defined in the General rules.
[1]. Section 6.3.3.3, "Rule evaluation order", in a draft of the SQL 2008 standard, having the local filename 5CD2-01-Framework-2006-01.pdf, p. 41 I have no idea where I got it. Google might know.