The error is due to that (although there is no BOOLEAN datatype in SQL Server) the expression in a CHECK constraint must result in a boolean value. Yours result in a bit value.
You can technically solve this but changing your CHECK constraint to:
CHECK(dbo.checkIfAuthorized(signedBy, 8) = CAS...
weird
The performance issue in both queries has nothing to do with CTE vs. temp table. I see there is a timing difference, but hear me out a little bit.
just delete
In the straight delete, you spend the most time deleting from tables, not selecting from them.
The waits in this query are all rela...
Paul, I was adding OP's last comment on the question. I noticed you deleted several comments but I think (at least the mention of FKs and triggers) might be helpful in the question, since there is no schema.
> Te Whatu Ora’s lawyer Paul White told the court that medical professionals have said a child with such a condition would have been treated several weeks ago in normal circumstances; while the parents’ lawyer Sue Grey – another prominent anti-vaccination campaigner – said the parents wanted better care than what the state was offering.
Oh pre-empted by an ypercube
But seriously, you're claiming not to have any antivax nutters in Chile?
It's fun to point out all the ways people can abuse UDFs, but that's not really answering the root of the question which is: "How do you ensure a user is authorized to do something in a manner that doesn't conflict with other things"
IMO (and backed by experience) this is an appropriate use for a UDF/check constraint combo and all the articles you link to won't really apply
The USER is enforced, but you need the row to exist in auth, not User.
Go into the fiddle, add a user, add type 8 and that user to auth, insert a row into your table, then try to remove the row from auth
This is a temporal constraint - you have to check the user is currently authorized with type 8. Otherwise once you grant type 8 you can never remove it
@Lamak I suppose it irks me a bit when these people are lucky enough to live where the required treatment is available for free and they're refusing it on the basis of who the blood needed might have come from. Seems a little off.
Perhaps there's some way to condense all this feedback on ypercube's solution into something with lasting value. Maybe in a way that allows community feedback and revision history. Helps future visitors to the site.
@PaulWhite if the auth row has been deleted in the meantime, the CHECK constraint would fail validation (I assume that the constraints are checked at restore, aren't they?)
@PaulWhite ok, then. I was probably confusing it in my mind, with backup/restores in Postgres where it would be CREATE TABLE + INSERTs, so they would be checked
@JoshDarnell I would but two things 1. the original question is due to a syntax error. 2. 13 people have upvoted an answer that lambasts UDFs and if I say "Hey, yes, you can't use a UDF in this case" what's to say the Anti-UDF League will just downvote or flood the question with comments akin to the chat?
@ErikDarling I've got the "Super Mom" mug filled with my morning latte so I think I'm ready to go
Plan Selection
You want to find the top 10 rows by transaction date descending, given a list of one or more users.
You already have an index to help find the users, with an included column to provide the join key to the detail table. You were expecting SQL Server to produce a plan that finds user...
For example
I knew that would get no love, but I wrote it anyway
One could also ask the question you wish had been asked, and self-answer that
I was watching Bluey (a kids show set in Australia) with my kids the other day. The characters were putting together a puzzle that was a map of the world, and the kid goes "New Zealand is missing" because the piece wasn't in the box. The dad said, "aw not again" and I thought that was an awesome subtle joke.
As for the downsides of UDFs in constraints, well there's not really a perfect solution in SQL Server for anything more complex than will fit sensibly in declarative RI. You're going to end up with code in a procedure, trigger, or function. Pretty much a personal choice, just needs to be an informed one.
I tend to disfavour the UDF option because the downsides aren't readily apparent to most people
@PaulWhite What are the downsides in doing what amounts to an existence check once upon insert? It'll never run during a select and wouldn't run again unless someone tried to update who authorized the transaction
I agree not to use them in any other instance. They're generally implemented very poorly the way people try to use them.
@bbaird Yes, I'm speaking in generalities, not about the specific example (which I haven't even read deeply). The performance aspect is a big part of it, of course. But there are other things like only ever changing columns referenced by the function, interactions with row-versioning isolation levels etc.
Like anything else, if one is sufficiently expert and can guarantee bad cases won't happen, they're fine
In principle they're not awful. In practice, SQL Server's lamentable implementation for scalar functions makes life considerably more complicated
I'll think about it later today - just wasted a good portion of my morning playing "What's making my monitor not work? " suspects include: the mini displayport to displayport adapter, the KVM, and the monitor itself.
I gave up and used a HDMI cable
I also have a ton of useless queries to write because the MDM ETL logic is written poorly and failing
I don't think I'd have a UDF on a table where that was a possibility or it would have to be one heck of a data fix to bother with (along with required documentation, testing, etc)
Yet other requirements like a table lock need to be explicit
I understand the perf advantage of course, I just would've thought it would need to be explicit
People hate on triggers too, but at least you have the option of properly coding them for multi-row changes
Likewise for a procedure, if you allow a TVP or other way to specify a multi-row source
Even for true OLTP-style single row ops, the UDF overhead becomes significant if you do a lot of ops
It might be different if Microsoft extended function inlining to constraints (and computed columns for that matter), but that seems unlikely at this point.
Perhaps Hekaton supports compiled functions in check constaints, idk offhand
@ypercubeᵀᴹ Let me know if you'd like some input around use cases - I use the UDF/constraint pattern for exactly two things (existence-type rules and exclusive subtypes). Also worth mentioning how to approach through SP logic.
Yeah, I see the appeal but it still gives me a bad taste. Similar to non-deterministic functions in check constraints and domains, we had a discussion here some weeks ago.
@ypercubeᵀᴹ Think of it this way: UDFs are a scalpel. They're a tool. You wouldn't use one to peel potatoes the same way you wouldn't use a vegetable peeler to perform an appendectomy.
A lot of the articles you link to are examples of people trying to use scalpels as peelers, blenders, and remote controls
Equally, a vegetable peeler is most often the right tool for the job
Another valid approach is to avoid invalid data entering the database in the first place. Users (including their applications) should not be able to add arbitrary data to tables. They shouldn't even be able to access the tables directly for reading let alone writing.
Which is just the stored procedure approach, I guess.
The idea being, in this case, that if a report entry exists it is because is was definitely authorised at the time it was created, so there's never a need to check it again, whether it remains in the base table or archived off to a history table.
On balance, there are just too many downsides for me to use scalar functions this way.
I suppose the links in the answer already address the lack of parallelism for any operation including that table (including index rebuilds).
You have to reference the column with the UDF check constraint on, but it's still a pretty general restriction. Anything that loads metadata for the column will do it.
paul's screenshot shows a parallel plan and then a non-parallel plan when the udf constraint is referenced
in sql server there's a restriction on queries that reference scalar udfs that they can't use a parallel plan. you'll see that when a scalar udf is in the query, or buried in a computed column or check constraint in a table referenced by the query (when the metadata is loaded)
USE StackOverflow2010;
GO
CREATE OR ALTER FUNCTION dbo.F (@i integer)
RETURNS integer
AS
BEGIN
RETURN 1;
END;
GO
-- Parallel
SELECT COUNT_BIG(*)
FROM dbo.Posts AS P
WHERE P.CommentCount > 100;
ALTER TABLE dbo.Posts
WITH CHECK ADD
CONSTRAINT CK_CommentCount
CHECK (dbo.F(CommentCount) = 1);
-- Serial
SELECT COUNT_BIG(*)
FROM dbo.Posts AS P
WHERE P.CommentCount > 100;
ALTER TABLE dbo.Posts
NOCHECK CONSTRAINT CK_CommentCount;
-- Parallel again
SELECT COUNT_BIG(*)
db<>fiddle doesn't support parallelism, so that's a SO demo
I don't normally see it or care because most of the queries don't use/need parallelism - but I added it to a situation where one could possibly see it (DWH type query) and yes, you're right
"...the bobbit worm stays hidden under tropical sands with just its five antennae poking out—waiting. When it senses prey above, it moves with speed and strength to grab them, sometimes splitting its fishy prey in half with its sharp teeth! It also injects them with a toxin to help break down its food to make it easier to digest."
Does anyone know in what case there's a notable drawback to persisting a computed column as opposed to not persisting (in SQL Server)? I could've sworn I read that once, maybe something Paul wrote somewhere? My mind is blanking...or I'm getting dumber by the day. 😬
I think it was execution plan-ish related. I know you can index non-persisted computed columns and reap the benefits of the index materializing the results. But I thought there was a notable drawback in persisting too for a different scenario.
@J.D. In this article, Paul mentions a potential downside to persisted computed columns when you also have TF 176 enabled.
> Note that the expression matching limitation only applies to persisted computed columns when trace flag 176 is active. If we make the computed column indexed but not persisted, expression matching works correctly.
The more obvious downside to adding a persisted computed column to an existing table is it might result in a lot of page splitting or forwarded records, along with heaps of log
I used to drive past this place when I would canoe on the Potomac - never got to see inside (DOD stuff, ya know) so it's cool to finally see it: youtube.com/watch?v=pir_muTzYM8