11

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.

  • Functions are very different in different platforms, but yes, there are fairly standard (i.e. Oracle-provided) add-on functions in Oracle for example, for sending email. And there are nondeterministic functions in SQL Server like NEWID and RAND, and functions in SQL Server can called extended procedures too. – Cade Roux Feb 21 '17 at 0:00
  • 3
    If the question is "does the SQL standard include functions that have side effects" I think the answer is no. If the question is "does the SQL standard allow functions to be written that have side effects" then I think the answer is yes. – a_horse_with_no_name Feb 21 '17 at 12:47
  • @a_horse_with_no_name, Thanks. My question was the former, about style and convention. – tinlyx Feb 21 '17 at 15:08
18

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.

-3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.