It would be great if, instead of (as well as) translating a scalar UDF to a relational expression, the engine could give the code to implement it as an in-line table-valued function.
There is a lot in master. Depends where the function is.
Found this gem:
create function sys.fn_numberOf1InVarBinary (@bm varbinary(128))
returns int
as
begin
declare @len int
declare @count int
declare @subbyte binary
set @count=0
set @len=DATALENGTH (@bm)
while (@len>0)
begin
set @subbyte=SUBSTRING(@bm, @len, 1)
set @count=@count+sys.fn_numberOf1InBinaryAfterLoc(@subbyte, 8)
set @len=@len-1
end
return @count
end
I don't think you can apply sp_helptext/OBJECT_DEFINITION to built-in T-SQL functions like OBJECT_ID or otherwise get their source code in T-SQL.
As for accepting unquoted string parameters just as well as quoted, I think that's just a peculiarity of the syntax, and it works with built-in functions but probably not with those created using CREATE FUNCTION.
It works with stored procedures too, by the way.
USE tempdb
GO
CREATE PROCEDURE dbo.TestProc (@StringParam varchar(100))
AS
SELECT @StringParam;
GO
EXECUTE dbo.TestProc test;
GO
DROP PROCEDURE dbo.TestProc;
GO
What causes it to be legal to pass an object name to the system stored procedure sp_helptext?
What mechanism converts the object name to a string?
e.g.
-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same as a string
sp_helptext 'myproc'
sp_helptext...
Ah now that I understand. Ok well I wasn't trying to be slippery, so "no".
Anyway, Clippy should pop up and say "it looks like you're trying to ruin the performance of your query. Try this equivalent in-line function instead."
@sp_BlitzErik Oh for sure the vast majority of scalar UDFs are just dumb because the writer didn't know better, and/or just (reasonably) expected performance to be at least OK.
@sp_BlitzErik Gets a bad rap. It's a pretty useful tool if you know how to use it and interpret/apply the results. It's really just missing index suggestions on roids, and people cope with them.
Hey isn't it amazing how many of these things are finally getting some attention now they can see some of the real problems for themselves at scale on Azure.
I feel like slapping Conor around the head with every scalar UDF AdventureWorks demo and Won't Fix Connect item submitted over the past couple of decades.
oh i just meant that a lot of the checks i had in blitzcache worked fine for a single statement but would fail direly in a stored proc or in a stored proc with more than a single statement
there have been plenty where i didn't have an existence check for a particular branch of xml or where i wasn't filtering or ordering the results so that if the offending bits were magically first they wouldn't get flagged
It's like when I used to play Eve... One of my characters was named "Theivy McBackstabber" and I stole from corporations... they always asked me why. It's literally in my name.
Diplomacy is a strategic board game created by Allan B. Calhamer in 1954 and released commercially in 1959. Its main distinctions from most board wargames are its negotiation phases (players spend much of their time forming and betraying alliances with other players and forming beneficial strategies) and the absence of dice and other game elements that produce random effects. Set in Europe before the beginning of World War I, Diplomacy is played by two to seven players, each controlling the armed forces of a major European power (or, with fewer players, multiple powers). Each player aims to move...
A strategy game or strategic game is a game (e.g. video or board game) in which the players' uncoerced, and often autonomous decision-making skills have a high significance in determining the outcome. Almost all strategy games require internal decision tree style thinking, and typically very high situational awareness.
The term "strategy" comes ultimately from Greek, (στρατηγια or strategia) meaning generalship. It differs from "tactics" in that it refers to the general scheme of things, whereas "tactics" refers to organization and execution.
== History ==
The history of turn-based strategy games...
I am not sure why the below join query is not returning any results.
SELECT * FROM table1 JOIN table2 ON table1.fieldid = table2.idfield
The below 2 queries return results so I am not sure why the join wont pick up any rows when i can clearly see that both tables have join field values in co...
I have table with a string field ID with values looking like this:
012345678
I want to select all the records from the ID field that begin with a 0 and have a letter in them, e.g 000A345B or 0A32C450. I also want to select records that begin with zero and contain a -, / or a space.
Note th...
@sp_BlitzErik The thought crossed my mind, but then I couldn't remember if it should be _Bin or _Bin2 or whether it's a good idea to use Latin1_* if their environment actually uses SQL_Latin1_* etc.
What do you think about a mysql-aio tag? there are at least a bazillion questions involving the innodb thread pool
I think what we need to consider doing is burning performance and optimization related tags, and the horrible postgresql-performance and pushing smaller more reasonable tags about what is actually be optimized or whatever.
Yeah, ok I'll drop it as I don't care about MySQL anyway. I just have a tendency to want to organize things when they become an epic mess and a lot of these questions are use-case independent but could all be answered with the same answer.