Functie versus opgeslagen procedure in SQL Server

Antwoord 3, autoriteit 25%

Functies en opgeslagen procedures hebben verschillende doelen. Hoewel het niet de beste analogie is, kunnen functies letterlijk worden gezien als elke andere functie die je in elke programmeertaal zou gebruiken, maar opgeslagen processen lijken meer op individuele programma’s of een batchscript.

Functies hebben normaal gesproken een uitgang en optioneel ingangen. De uitvoer kan dan worden gebruikt als invoer voor een andere functie (een ingebouwde SQL Server zoals DATEDIFF, LEN, enz.) of als een predikaat voor een SQL-query – bijv. SELECT a, b, dbo.MyFunction(c) FROM tableof SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Opgeslagen procedures worden gebruikt om SQL-query’s samen te binden in een transactie en om te communiceren met de buitenwereld. Frameworks zoals ADO.NET, enz. kunnen een functie niet rechtstreeks aanroepen, maar ze kunnen een opgeslagen proces wel rechtstreeks aanroepen.

Functies hebben echter een verborgen gevaar: ze kunnen worden misbruikt en nogal vervelende prestatieproblemen veroorzaken: overweeg deze vraag:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Waar MyFunction is gedeclareerd als:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER
   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue
   RETURN @retval
END

Wat hier gebeurt, is dat de functie MyFunction wordt aangeroepen voor elke rij in de tabel MyTable. Als MyTable 1000 rijen heeft, dan zijn dat nog eens 1000 ad-hocquery’s op de database. Evenzo, als de functie wordt aangeroepen wanneer deze is opgegeven in de kolomspecificatie, wordt de functie aangeroepen voor elke rij die wordt geretourneerd door de SELECT.

Dus je moet voorzichtig zijn met het schrijven van functies. Als je SELECT uit een tabel in een functie doet, moet je jezelf afvragen of het beter kan worden uitgevoerd met een JOIN in de bovenliggende opgeslagen proc of een andere SQL-constructie (zoals CASE … WHEN … ELSE … EINDE).

Antwoord 4, autoriteit 9%

Verschillen tussen opgeslagen procedures en door de gebruiker gedefinieerde functies:

• Opgeslagen procedures kunnen niet worden gebruikt in Select-instructies.
• Opgeslagen procedures ondersteunen Uitgestelde naamomzetting.
• Opgeslagen procedures worden over het algemeen gebruikt voor het uitvoeren van bedrijfslogica.
• Opgeslagen procedures kunnen elk datatype retourneren.
• Opgeslagen procedures kunnen grotere aantallen invoerparameters accepteren dan door de gebruiker gedefinieerde functies. Opgeslagen procedures kunnen maximaal 21.000 invoerparameters hebben.
• Opgeslagen procedures kunnen dynamische SQL uitvoeren.
• Opgeslagen procedures ondersteunen foutafhandeling.
• Niet-deterministische functies kunnen worden gebruikt in opgeslagen procedures.

• Door de gebruiker gedefinieerde functies kunnen worden gebruikt in Select-instructies.
• Door de gebruiker gedefinieerde functies ondersteunen geen uitgestelde naamomzetting.
• Door de gebruiker gedefinieerde functies worden over het algemeen gebruikt voor berekeningen.
• Door de gebruiker gedefinieerde functies moeten een waarde retourneren.
• Door de gebruiker gedefinieerde functies kunnen geen afbeeldingen retourneren.
• Door de gebruiker gedefinieerde functies accepteren kleinere aantallen invoerparameters dan opgeslagen procedures. UDF’s kunnen maximaal 1.023 invoerparameters hebben.
• Tijdelijke tabellen kunnen niet worden gebruikt in door de gebruiker gedefinieerde functies.
• Door de gebruiker gedefinieerde functies kunnen geen dynamische SQL uitvoeren.
• Door de gebruiker gedefinieerde functies ondersteunen geen foutafhandeling. RAISEERROROF @@ERRORzijn niet toegestaan in UDF’s.
• Niet-deterministische functies kunnen niet worden gebruikt in UDF’s. GETDATE()kan bijvoorbeeld niet worden gebruikt in UDF’s.

Antwoord 5, autoriteit 8%

Schrijf een door de gebruiker gedefinieerde functie wanneer u een waarde wilt berekenen en retourneren voor gebruik in andere SQL-instructies; schrijf een opgeslagen procedure wanneer u dat wilt, in plaats daarvan moet u een mogelijk complexe set SQL-instructies groeperen. Dit zijn tenslotte twee behoorlijk verschillende gebruiksscenario’s!

Antwoord 6, autoriteit 6%

             STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can not go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
                                       |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                       |   statements.

Antwoord 7, autoriteit 4%

Basisverschil

Functie moet een waarde retourneren, maar in Opgeslagen procedure is dit optioneel (procedure kan nul of n waarden retourneren).

Functies kunnen er alleen invoerparameters voor hebben, terwijl Procedures invoer-/uitvoerparameters kunnen hebben.

Functie heeft één invoerparameter nodig, dit is verplicht, maar Opgeslagen procedure kan o tot n invoerparameters bevatten.

Functies kunnen worden aangeroepen vanuit Procedure, terwijl Procedures niet kunnen worden aangeroepen vanuit Functie.

Geavanceerd verschil

Procedure staat zowel SELECT als DML(INSERT/UPDATE/DELETE)-instructie toe, terwijl Function alleen SELECT-instructie erin toestaat.

Procedures kunnen niet worden gebruikt in een SELECT-instructie, terwijl Function kan worden ingesloten in een SELECT-instructie.

Opgeslagen procedures kunnen nergens in de sectie WHERE/HAVING/SELECT in de SQL-instructies worden gebruikt, terwijl Function dat wel kan.

Functies die tabellen retourneren, kunnen worden behandeld als een andere rijenset. Dit kan worden gebruikt in JOIN’s met andere tabellen.

Inline-functie kan worden gezien als weergaven die parameters nodig hebben en kunnen worden gebruikt in JOIN’s en andere Rowset-bewerkingen.

Uitzondering kan worden afgehandeld door een try-catch-blok in een procedure, terwijl een try-catch-blok niet kan worden gebruikt in een functie.

We kunnen gaan voor transactiebeheer in procedure, terwijl we niet in functie kunnen gaan.

bron

Antwoord 8, autoriteit 3%

een door de gebruiker gedefinieerde functie is een belangrijk hulpmiddel dat beschikbaar is voor een sql-serverprogrammeur. Je kunt het inline gebruiken in een SQL-instructie, zoals zo

SELECT a, lookupValue(b), c FROM customers 

waar lookupValueeen UDF zal zijn. Dit soort functionaliteit is niet mogelijk bij gebruik van een opgeslagen procedure. Tegelijkertijd kun je bepaalde dingen niet doen binnen een UDF. Het belangrijkste om hier te onthouden is dat UDF’s:

• kan geen permanente wijzigingen aanbrengen
• kan gegevens niet wijzigen

een opgeslagen procedure kan die dingen doen.

Voor mij is het inline gebruik van een UDF het belangrijkste gebruik van een UDF.

Antwoord 9, autoriteit 2%

Opgeslagen proceduresworden gebruikt als scripts. Ze voeren een reeks opdrachten voor u uit en u kunt ze plannen om op bepaalde tijden te worden uitgevoerd. Voert meestal meerdere DML-instructies uit, zoals INSERT, UPDATE, DELETE, enz. of zelfs SELECT.

Functiesworden gebruikt als methoden.Je geeft het iets door en het geeft een resultaat. Moet klein en snel zijn – doet het on-the-fly. Meestal gebruikt in een SELECT-instructie.

Antwoord 10

Opgeslagen procedure:

• Is als een miniatuurprogramma in SQL Server.
• Kan zo simpel zijn als een select statement, of zo complex als een long
  script dat gegevens toevoegt, verwijdert, bijwerkt en/of leest van meerdere
  tabellen in een database.
• (Kan lussen en cursors implementeren, waarmee u beide kunt werken
  kleinere resultaten of rij voor rij bewerkingen op gegevens.)
• Moet worden aangeroepen met de instructie execof EXECUTE.
• Retourneert tabelvariabelen, maar we kunnen de parameter OUTniet gebruiken.
• Ondersteunt transacties.

Functie:

• Kan niet worden gebruikt om records bij te werken, te verwijderen of toe te voegen aan de database.
• Retourneert eenvoudig een enkele waarde of een tabelwaarde.

• Kan alleen worden gebruikt om records te selecteren. Het kan echter worden genoemd
  heel gemakkelijk vanuit standaard SQL, zoals:

  SELECT dbo.functionname('Parameter1')
  

  of

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• Voor eenvoudige herbruikbare selectiebewerkingen kunnen functies de code vereenvoudigen.
  Wees op uw hoede voor het gebruik van JOIN-clausules in uw functies. Als jouw
  functie heeft een JOIN-clausule en je roept het aan vanuit een andere select
  statement dat meerdere resultaten retourneert, zal die functieaanroep JOIN
  die tabellen samen voor elkeregel geretourneerd in de resultatenset. Dus
  hoewel ze nuttig kunnen zijn bij het vereenvoudigen van enige logica, kunnen ze ook een
  prestatie knelpunt als ze niet correct worden gebruikt.

• Retourneert de waarden met de parameter OUT.
• Ondersteunt geen transacties.

Antwoord 11

Door gebruiker gedefinieerde functie.

1. Functie moet een waarde retourneren.
2. Sta alleen Select-statements toe, het staat ons niet toe om DML-statements te gebruiken.
3. Het laat alleen invoerparameters toe, ondersteunt geen uitvoerparameters.
4. We kunnen geen try-catch-blokken gebruiken.
5. Transacties zijn niet toegestaan binnen functies.
6. We kunnen alleen tabelvariabelen gebruiken, tijdelijke tabellen zijn niet toegestaan.
7. Opgeslagen procedures kunnen niet worden aangeroepen vanuit een functie.
8. Functies kunnen worden aangeroepen vanuit een select-statement.
9. Een UDF kan worden gebruikt in de join-clausule als resultaatset.

Opgeslagen procedure

1. Opgeslagen procedure kan al dan niet waarden retourneren.
2. Kan zowel select-statements als DML-statements hebben, zoals insert, update, delete enzovoort
3. Het kan zowel invoer- als uitvoerparameters hebben.
4. Voor het afhandelen van uitzonderingen kunnen we try-catch-blokken gebruiken.
5. Kan transacties gebruiken binnen Opgeslagen procedures.
6. Kan zowel tabelvariabelen als tijdelijke tabel erin gebruiken.
7. Opgeslagen procedures kunnen functies aanroepen.
8. Procedures kunnen niet worden aangeroepen vanuit Select/Where/Having-instructies enzovoort. De instructie Execute/Exec kan worden gebruikt om de opgeslagen procedure aan te roepen/uit te voeren.
9. Procedures kunnen niet worden gebruikt in Join-clausule

Antwoord 12

Om te beslissen wanneer te gebruiken, wat de volgende punten kunnen helpen-

1. Opgeslagen procedures kunnen geen tabelvariabele retourneren waar een functie dat wel kan.

2. U kunt opgeslagen procedures gebruiken om de parameters van de serveromgeving te wijzigen, terwijl u functies niet kunt gebruiken.

proost

Antwoord 13

SQL Server-functies, zoals cursors, zijn bedoeld om als laatste wapen te worden gebruikt! Ze hebben prestatieproblemen en daarom moet het gebruik van een functie met tabelwaarde zoveel mogelijk worden vermeden. Praten over prestaties is praten over een tabel met meer dan 1.000.000 records gehost op een server op een hardware van de middenklasse; anders hoeft u zich geen zorgen te maken over de prestatiehit die door de functies wordt veroorzaakt.

1. Gebruik nooit een functie om een resultatenset terug te sturen naar een externe code (zoals ADO.Net)
2. Gebruik de combinatie van views/opgeslagen processen zoveel mogelijk. u kunt toekomstige problemen met de groeiprestaties herstellen met behulp van de suggesties die DTA (Database Tuning Adviser) u geeft (zoals geïndexeerde weergaven en statistieken) –soms!

zie voor meer informatie: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

Antwoord 14

Begin met functies die een enkele waarde retourneren. Het leuke is dat je veelgebruikte code in een functie kunt stoppen en ze als een kolom in een resultatenset kunt retourneren.

Dan zou je een functie kunnen gebruiken voor een geparametriseerde lijst met steden. dbo.GetCitiesIn(“NY”) Dat retourneert een tabel die als join kan worden gebruikt.

Het is een manier om code te ordenen. Weten wanneer iets herbruikbaar is en wanneer het tijdverspilling is, kun je alleen verkrijgen door vallen en opstaan en ervaring.

Functies zijn ook een goed idee in SQL Server. Ze zijn sneller en kunnen behoorlijk krachtig zijn. Inline en directe selecties. Pas op dat u niet te veel gebruikt.

Antwoord 15

Hier is een praktische reden om functies te verkiezen boven opgeslagen procedures. Als u een opgeslagen procedure hebt die de resultaten van een andere opgeslagen procedure nodig heeft, moet u een insert-exec-instructie gebruiken. Dit betekent dat u een tijdelijke tabel moet maken en een exec-instructie moet gebruiken om de resultaten van de opgeslagen procedure in de tijdelijke tabel in te voegen. Het is rommelig. Een probleem hiermee is dat insert-execs niet kunnen worden genest.

Als je vastzit met opgeslagen procedures die andere opgeslagen procedures aanroepen, kun je hier tegenaan lopen. Als de geneste opgeslagen procedure gewoon een gegevensset retourneert, kan deze worden vervangen door een functie met tabelwaarde en krijgt u deze fout niet meer.

(dit is nog een reden waarom we bedrijfslogica uit de database moeten weren)

Antwoord 16

Ik realiseer me dat dit een heel oude vraag is, maar ik zie in geen van de antwoorden één cruciaal aspect genoemd: inlining in het queryplan.

Functies kunnen zijn…

1. Scalair:

   CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

2. Tabelwaarde met meerdere verklaringen:

   CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

3. Inline tabelwaarde:

   CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

De derde soort (inline tabelwaarde) wordt door de query-optimizer in wezen behandeld als (geparametriseerde) views, wat betekent dat het verwijzen naar de functie van uw query vergelijkbaar is met het kopiëren en plakken van de SQL-tekst van de functie (zonder daadwerkelijk te kopiëren en plakken ), wat leidt tot de volgende voordelen:

• De queryplanner kan de uitvoering van de inline-functie optimaliseren, net als elke andere subquery (bijv. ongebruikte kolommen elimineren, predikaten naar beneden duwen, verschillende JOIN-strategieën kiezen, enz.).
• Het combineren van verschillende inline-functies vereist niet dat het resultaat van de eerste wordt gematerialiseerd voordat het naar de volgende wordt gevoerd.

Het bovenstaande kan leiden tot potentieel aanzienlijke prestatiebesparingen, vooral bij het combineren van meerdere functieniveaus.

OPMERKING: het lijkt erop dat SQL Server 2019 een vorm van scalar functie ook inlining.

Antwoord 17

• Het is verplicht voor Functie om een waarde te retourneren terwijl dit niet voor de opgeslagen procedure is.
• Selecteer instructies die alleen in UDF worden geaccepteerd, terwijl DML-instructies niet vereist zijn.
• Opgeslagen procedure accepteert alle instructies en DML-instructies.
• UDF staat alleen invoer toe en geen uitvoer.
• Opgeslagen procedure staat zowel invoer als uitvoer toe.
• Vangblokken kunnen niet worden gebruikt in UDF, maar kunnen worden gebruikt in de opgeslagen procedure.
• Geen transacties toegestaan in functies in UDF, maar in opgeslagen procedure zijn ze toegestaan.
• Alleen tabelvariabelen kunnen worden gebruikt in UDF en geen tijdelijke tabellen.
• Opgeslagen procedure staat zowel tabelvariabelen als tijdelijke tabellen toe.
• UDF staat niet toe dat opgeslagen procedures worden aangeroepen vanuit functies, terwijl opgeslagen procedures het aanroepen van functies toestaan.
• UDF wordt gebruikt in de join-clausule, terwijl opgeslagen procedures niet kunnen worden gebruikt in de join-clausule.
• Opgeslagen procedure zorgt altijd voor terugkeer naar nul. UDF daarentegen heeft waarden die moeten terugkomen – terug naar een vooraf bepaald punt.

Antwoord 18

Mssql opgeslagen procedure vs functie:

Antwoord 19

• Functies kunnen worden gebruikt in een select-statement waar procedures dat niet kunnen.

• Opgeslagen procedure neemt zowel invoer- als uitvoerparameters, maar Functions neemt alleen invoerparameters.

• Functies kunnen geen waarden van het type text, ntext, image & tijdstempels waar procedures dat kunnen.

• Functies kunnen worden gebruikt als door de gebruiker gedefinieerde datatypes in tabel maken, maar procedures niet.

***Bijv.:-maak table <tablename>(name varchar(10),salary getsal(name))

Hier is getal een door de gebruiker gedefinieerde functie die een salaristype retourneert, wanneer een tabel wordt gemaakt, wordt er geen opslag toegewezen voor het salaristype en wordt de getal-functie ook niet uitgevoerd. Maar wanneer we enkele waarden uit deze tabel ophalen, krijgt de getal-functie get’s uitgevoerd en de terugkeer
Type wordt geretourneerd als de resultaatset.

Antwoord 20

Over het algemeen is het gebruik van opgeslagen procedures beter voor de prestaties.
Als u bijvoorbeeld in eerdere versies van SQL Server de functie in de JOIN-voorwaarde plaatst, is de kardinaliteitsschatting 1 (vóór SQL 2012) en 100 (na SQL 2012 en vóór SQL 2017) en kan de engine een slecht uitvoeringsplan genereren.

Ook als u het in de WHERE-clausule plaatst, kan de SQL Engine een slecht uitvoeringsplan genereren.

Met SQL 2017 introduceerde Microsoft de functie interleaved uitvoering om een nauwkeurigere schatting te maken, maar de opgeslagen procedure blijft de beste oplossing.

Zie voor meer details het volgende artikel van Joe Sack
https: //techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417

Antwoord 21

In SQL Server zijn functies en opgeslagen procedures twee verschillende typen entiteiten.

Functie:In de SQL Server-database worden de functies gebruikt om bepaalde acties uit te voeren en de actie geeft onmiddellijk een resultaat.
Er zijn twee soorten functies:

1. Systeem gedefinieerd

2. Door gebruiker gedefinieerd

Opgeslagen procedures:In SQL Server worden de opgeslagen procedures op de server opgeslagen en kunnen nul, enkele en meerdere waarden retourneren.
Opgeslagen procedures zijn er in twee soorten:

1. In het systeem opgeslagen procedures
2. Door de gebruiker gedefinieerde procedures