CTE och funktioner

Jag har gjort ett litet script som tar fram datum för närmaste veckodag:

set datefirst 1;

DECLARE @startdate DATE;
DECLARE @daytofind INT;
SET @startdate = getdate()
SET @daytofind = 2; -- tuesday

WITH myWeek AS
(SELECT @startdate AS myDay, DATEPART(WEEKDAY, @startdate) as MyDayofWeek
UNION ALL
SELECT DATEADD(dd,1,myDay) AS myDay, DATEPART(WEEKDAY, DATEADD(dd,1,myDay)) as MyDayofWeek FROM myWeek WHERE DATEDIFF(dd,@startdate,myday) < 6)
SELECT myDay FROM myWeek WHERE MyDayofWeek = @daytofind


Nu vill jag översätta detta till en funktion, men det verkar inte funka. Funktioner och CTE verkar inte funka så bra tillsammans.

Funktioner i SQL server kan inte a några sidoeffekter, dvs de kan inte skapa temporära tabeller eller stoppa i data i tabeller, endast läsa dessa.

Med detta sagt borde du helt sonika kunna skapa en table-valued function (och inte scalar) som returnerar det du vill ha, borde ju räcka med att ta bort "with" för att din CTE ska bli en vanlig simpel select, sen borde du kunna proppa i den i en tabell-funktion.

Förutom det Proton kom fram till, vill jag att du tänker till ett extra varv hur du sedan kommer använda funktionen. Hemmasnickrade funktioner kan sänka allt vad prestanda heter om de används felaktigt. Tänk att databaser jobbar som mest effektivt när de jobbar set-baserat och kan använda index på ett vettigt sätt. Är det så att den där funktionen har en högfrekvent användning, skulle jag nog göra en uppslagstabell som uppdateras en gång om dagen istället.

Varför inte en stored procedure?

Därför att resultaten från en stored procedure kan inte utan vidare joinas ihop med andra tabeller på samma sätt som en CTE eller en UDF kan, de är inte riktigt avsedda att användas för det heller.

Använder man en SP kommer man för att kunna accessa datat den ger ifrån sig behöva ta till OÅENROWSET eller wrappa den i en egen UDF, vilket gör att då är vi tillbaka på ruta ett.

Det vet jag inte om jag kan hålla med om. Bara att lägga in i en temp-tabell så är saken biff.

UDFer i SQL server kan inte skapa tabeller, bortsett ifrån sådana som är lokala för funktionen, och då endast tabellvariabler, ej temporära tabeller.

https://docs.microsoft.com/en-us/sql...ql-server-2017

Hur menar du ett en temporär tabell skulle göra saken biff?

För övrigt var frågan om användning av UDFer eller CTE-uttryck, inte om stored procedures.

CTE eller UDF – Finns det rationella skäl att föredra det ena?
Även om trådstarten egentligen handlar om blandning av (user defined) functions och CTE så tycker jag att frågeställningen vad man ska välja är principiellt intressant.
Jag har inte hållit på med detta tillräckligt mycket för att ha en bestämd åsikt, men vore ändå tacksam för råd från (dig Proton eller någon annan som har hållit på mer än jag med detta) om hur man ska resonera.
Jag har fått rådet att undvika UDF-er till förmån för CTE om möjligt, men har använt UDF så lite så jag kan inte riktigt motivera det ena eller det andra. CTE, som är definierat sedan ANSI-99, finns t.ex. i Postgres sedan 7.8 (2007), i MSSQL i SQL Server f.o.m. 2008 R2, Oracle (sedan ca 2008) och i MySQL sedan 8 (ca 2016) kan väl anses som etablerat. Oracle (och några till) har tidigare haft "CONNECT" som liknar CTE men inte är lika.
Därutöver har vi stored procedures, tillsammans tre (eller möjligen fyra) olika metoder.

Lätt att läsa, snabbt och utrymmeseffektivt
Mina frågor:
– Kan man ge generella råd att försöka använda i första hand en metod?
– Kan man säga typfall då det ena eller det andra är att föredra?
– Kan man säga typfall då en viss teknik är överlägsen i effektivitet?

Jag har "snöat in" lite på CTE-er. Kan man göra det eller måste man ha UDF-er och stored procedures som alternativ?

Jag har googlat lite och får intrycket att olika leverantörer av databasmotorer ger lite olika budskap. Kanske är CTE-er lite för nytt för "gamla SQL-rävar"?

–

Jag hade nog i längsta möjliga mån försökt undvika UDFer och istället använt vyer och CTE där det är möjligt.

Den främsta förklaringen till det är att relationsdatabaser inte är bra på att hantera ett och ett objekt utan de vill ha mängder att jobba med, dessutom kommer inte sql server att använda index om man använder udfer, inte ens på de udfer som returnerar tabelldata, utan där kommer optimeraren att använda en table lookup.

Det är väl ok på mindre datamängder, men det kan få rejäla prestandakonsekvenser på större datamängder, detta ser man om man kollar på den execution plan sql server genererar, man kommer även upptäcka det med sql sever profiler.

Jag har varit med om fall där man från att ha använt en UDF för att ta fram en status övergått till att joina in en vy som gjorde samma sak kapat körtiden med minst 50% för just den frågan.

Anledningen var att SQL server tvingades för varje rad den hittade köra igenom funktionen och använda resultatet för att avgöra om raden skulle med eller ej. Inte speciellt effektivt.


Så, i de fall det verkligen inte går att använda vyer eller cte kan man ju ta till en UDF, men de är rätt få.

Stored procedures och CTEer är ju inte riktigt samma sak. CTE används ju för att ta fram data du kan joina in i en fråga, en SP packar ju ihop logik och ger dig ett resultat av exekveringen, en SP är det ju inte meningen att du ska joina in i någon fråga, utan den ska ju anropas nånstans ifrån och sedan ge dig ett resultat. Att det sedan kan vara en annan SP som anropar den är ju något annat.

Tack för utförligt svar!
Då känner jag mig lite mindre insnöad.
Sedan jag började använda CTE-er lite har dom vuxit för mig. Först var det ett tänk som jag inte var van vid, men när jag blev vän med tänket så gillar jag det verkligen. Man kan göra saker som jag tidigare varit benägen att göra i applikationen i stället för att låta databasmotorn sköta det.
Det kanske är en dålig jämförelse, men första aha-upplevelsen som jag fick var ungefär så här:
– Det här som jag vill fråga, finns ju inte i databasen...
– Med en CTE kan jag ju göra så att det "ser ut" att finnas i databasen!

Med användning av rekursion kan kraften i CTE-er växa ytterligare.

–

Jag håller inte med. Såhär resonerar jag:

1. Skript utan datum

2. Exec stored procedure into temp-tabell i skriptet

3. Använd temptabell med datum i samma skript.

Men jag kanske missuppfattade frågan?

Där har du ju två steg, du joinar inte in resultatet från SPn utan du proppar i det i nån jäkla temporär tabell som du sen utför operationer på, men det är inte att joina.

För övrigt så har du (ett halvår senare) fortfarande inte förstått grundfrågan ser det ut som, det var fråga om att använda UDFer eller CTEer, inte nåt svammel om SPar.