Are there standard SQL functions with side effects?

Question

Do the SQL standards define functions with side-effects?

For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like

SELECT myfunction(params...);

I've seen these sometimes, but I am just curious if the SQL standards do the same.

---

_{* This is not a question about PostgreSQL specifically. I'm only using the side-effects examples I see in PostgreSQL.}

Answer 1

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.

Answer 2

I can only speak definitively regarding SQL Server, and it appears this is not consistent across all database implementations. But in SQL Server, functions may not produce side effects. This is a hard and fast rule I've tried to circumvent a number of times with no success.

If you are thinking about functions in the general sense, there are SQL modules that allow side effects (for example stored procedures) but user-defined functions do not.

There is a saying "Clever Solutions Don't Scale" which is especially true with Microsoft products. I've seen a number of clever workarounds in early versions of SQL Server that became obsolete in later versions, because MS added them as true features.

The ones that never became features, honestly, never became features because they fundamentally broke some aspect of T-SQL development. Side effects in functions is one of them.