उपयोगकर्ता-परिभाषित फ़ंक्शन बनाएं

पूरा किया

उपयोगकर्ता-परिभाषित फ़ंक्शन (यूडीएफ) संग्रहीत कार्यविधियों के समान होते हैं जिसमें वे डेटाबेस में तालिकाओं से अलग संग्रहीत होते हैं। ये फ़ंक्शन पैरामीटर स्वीकार करते हैं, कोई क्रिया निष्पादित करते हैं, और फिर क्रिया परिणाम को एकल (स्केलर) मान या परिणाम सेट (तालिका-मान्य) के रूप में लौटाते हैं. फिर आप SELECT स्टेटमेंट लिखते समय टेबल के स्थान पर फ़ंक्शन का उपयोग कर सकते हैं। उपयोगकर्ता-परिभाषित फ़ंक्शन गणना करने और उस परिणाम को किसी अन्य कथन के भीतर उपयोग करने के लिए होते हैं। जबकि संग्रहीत कार्यविधियाँ फ़ंक्शन और कथन को समाहित कर सकती हैं, और डेटाबेस के भीतर डेटा को संशोधित भी कर सकती हैं।

हम तीन प्रकार के उपयोगकर्ता-परिभाषित कार्यों की समीक्षा करेंगे। विभिन्न कार्यों के अधिक विवरण के लिए, T-SQL संदर्भ दस्तावेज़ीकरण की समीक्षा करें।

इनलाइन तालिका-मूल्यवान फ़ंक्शन

इनलाइन टेबल-वैल्यू फ़ंक्शंस (TVF) SELECT स्टेटमेंट के आधार पर बनाया गया सबसे सरल फ़ंक्शन है, और वे प्रदर्शन के लिए पसंदीदा विकल्प हैं।

निम्न उदाहरण में, एक तालिका-मान फ़ंक्शन unitprice के लिए इनपुट पैरामीटर के साथ बनाया जाता है।

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

जब टेबल-वैल्यू फ़ंक्शन पैरामीटर के मान के साथ चलाया जाता है, तो इस मान से अधिक यूनिट मूल्य वाले सभी उत्पाद लौटाए जाएंगे।

निम्न कोड तालिका के स्थान पर तालिका-मान फ़ंक्शन का उपयोग करता है।

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

बहु-कथन तालिका-मूल्यवान कार्य

इनलाइन TVF के विपरीत, एक मल्टी-स्टेटमेंट टेबल-वैल्यू फ़ंक्शन (MSTVF) में एक से अधिक स्टेटमेंट हो सकते हैं और इसमें अलग-अलग सिंटैक्स आवश्यकताएं हो सकती हैं।

ध्यान दें कि निम्नलिखित कोड में, हम RETURN के अलावा BEGIN/END का उपयोग कैसे करते हैं:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

एक बार बनाने के बाद, आप ऊपर दिए गए पिछले इनलाइन फ़ंक्शन की तरह ही तालिका के स्थान पर एमएसटीवीएफ का संदर्भ देते हैं। आप FROM क्लॉज में आउटपुट का संदर्भ भी दे सकते हैं और इसे अन्य तालिकाओं के साथ जोड़ सकते हैं।

SELECT *
FROM Sales.mstvf_OrderStatus();

प्रदर्शन विचार

क्वेरी ऑप्टिमाइज़र यह अनुमान लगाने में असमर्थ है कि बहु-कथन तालिका-मान फ़ंक्शन के लिए कितनी पंक्तियाँ वापस आएंगी, लेकिन इनलाइन तालिका-मान फ़ंक्शन के साथ वापस आ सकती हैं। इसलिए, बेहतर प्रदर्शन के लिए जब संभव हो इनलाइन टीवीएफ का उपयोग करें। यदि आपको अन्य तालिकाओं के साथ एमएसटीवीएफ में शामिल होने की आवश्यकता नहीं है और / या आप जानते हैं कि परिणाम केवल कुछ पंक्तियां होंगे, तो प्रदर्शन प्रभाव उतना संबंधित नहीं है। यदि आप एक बड़े परिणाम सेट की अपेक्षा करते हैं और अन्य तालिकाओं के साथ जुड़ने की आवश्यकता है, तो इसके बजाय परिणामों को संग्रहीत करने के लिए एक अस्थायी तालिका का उपयोग करने पर विचार करें और फिर अस्थायी तालिका में शामिल हों।

SQL सर्वर संस्करण 2017 और उच्च संस्करण में, Microsoft MSTVF के लिए प्रदर्शन को बेहतर बनाने के लिए इंटेलिजेंट क्वेरी संसाधन के लिए सुविधाएँ प्रस्तुत किया। T-SQL संदर्भ दस्तावेज़ में इंटेलीजेंट क्वेरी संसाधन सुविधाओं के बारे में अधिक विवरण देखें।

स्केलर उपयोगकर्ता-परिभाषित फ़ंक्शन

एक स्केलर उपयोगकर्ता-परिभाषित फ़ंक्शन तालिका-मूल्यवान कार्यों के विपरीत केवल एक मान देता है और इसलिए अक्सर सरल, लगातार बयानों के लिए उपयोग किया जाता है।

किसी निश्चित दिन पर किसी विशिष्ट उत्पाद के लिए उत्पाद सूची मूल्य प्राप्त करने के लिए यहां एक उदाहरण दिया गया है:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

इस फ़ंक्शन के लिए, मान प्राप्त करने के लिए दोनों पैरामीटर प्रदान किए जाने चाहिए। फ़ंक्शन के आधार पर, आप फ़ंक्शन को SELECT कथन में अधिक जटिल क्वेरी में सूचीबद्ध कर सकते हैं.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

संदर्भित वस्तुओं के लिए फ़ंक्शन बाइंड करें

फ़ंक्शन बनाते समय स्कीमाबाइंडिंग वैकल्पिक है। जब आप SCHEMABINDING निर्दिष्ट करते हैं, तो यह फ़ंक्शन को संदर्भित ऑब्जेक्ट्स से बांधता है, और फिर ऑब्जेक्ट्स को फ़ंक्शन को संशोधित किए बिना संशोधित नहीं किया जा सकता है। ऑब्जेक्ट को संशोधित करने से पहले निर्भरताओं को हटाने के लिए फ़ंक्शन को पहले संशोधित या ड्रॉप किया जाना चाहिए।

SCHEMABINDING निम्न में से कोई भी होने पर हटा दिया जाता है:

• फ़ंक्शन गिरा दिया गया है
• फ़ंक्शन को SCHEMABINDING निर्दिष्ट किए बिना ALTER कथन के साथ संशोधित किया गया है