SQL सर्वर फ़ंक्शंस में परिचय, उनके लाभ और कमियां
SQL फ़ंक्शन क्या है?
SQL सर्वर फ़ंक्शंस का उपयोग एकल (स्केलर) मान, या तालिकाओं को वापस करने के लिए किया जा सकता है, T-SQL या CLR (सामान्य भाषा रन-टाइम) रूटीन का उपयोग करके और अक्सर सामान्य कोड में आप जितना उपयोग करना चाहते हैं उससे अधिक जटिल गणना करते हैं।
इनलाइन कोड के बजाय किसी फ़ंक्शन का उपयोग करना कब एक अच्छा विचार है?
अच्छा उपयोग
कार्यों का उपयोग विचारों को बदलने के लिए किया जा सकता है (एक तालिका लौटाएं), एक तालिका में एक परिकलित कॉलम के रूप में, लगातार लुकअप क्रियाएं करें या बस अपने कोड को संशोधित करने के लिए जो आवश्यक परिवर्तनों को कम करने में मदद कर सकता है।
खराब उपयोग
हम इसे हर समय देखते हैं, लेकिन जब आप बड़े डेटासेट के साथ काम कर रहे हों, तो शामिल होने के स्थान पर लुकअप डेटा वापस करने के लिए फ़ंक्शंस का उपयोग नहीं किया जाना चाहिए। प्रत्येक पंक्ति एक ही फ़ंक्शन को कॉल करेगी, भले ही वह पहले से ही उस मान का सामना कर चुकी हो। इन मामलों में, एक जॉइन का उपयोग करें।
स्केलर फ़ंक्शन उदाहरण
स्केलर फ़ंक्शंस का उपयोग पंक्ति आधारित पुन: स्वरूपण या गणना जैसे तर्क करने के लिए सबसे अच्छा किया जाता है क्योंकि उनकी प्रकृति के अनुसार उन्हें प्रत्येक पंक्ति के लिए बुलाया जाता है, उनका उपयोग किसी अन्य तालिका में डेटा देखने के लिए किया जा सकता है, लेकिन सामान्य तौर पर, आप इसका उपयोग करके बेहतर प्रदर्शन प्राप्त करेंगे एक शामिल हों। इसके लिए हम निम्न लिंक पर अपने गेट एज फंक्शन को देख सकते हैं।
फॉर्म भरते समय किसी की उम्र को स्टोर करने का कोई मतलब नहीं होगा, क्योंकि जब डेटा बाद में पूछताछ की जाएगी तो यह पुराना हो जाएगा। एक बेहतर विकल्प यह होगा कि आप जन्मतिथि को पकड़ लें और तुरंत उसकी गणना कर लें। हमारे फ़ंक्शन में हमने तब तक एक फ़ील्ड जोड़ा है, जिसका उपयोग किसी गणना को बैकडेट करने के लिए किया जा सकता है, या शायद अधिक उदास रूप से, मृत्यु के समय की आयु की गणना करें (यह फ़ंक्शन एनएचएस अनुबंध के लिए बढ़ाया गया था)।
Example
CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDस्केलर फ़ंक्शन उदाहरण
एक काल्पनिक तालिका से इसका उपयोग करने के लिए हम बस इसका उपयोग करेंगे, जो या तो वर्तमान आयु या मृत्यु की आयु प्रदान करेगा।
Use in a select statement
SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]स्केलर फ़ंक्शन उदाहरण
लाभ : लगातार, मॉड्यूलर, अधिक कॉम्पैक्ट, संभावित रूप से परिवर्तनों की संख्या को कम करता है
नुकसान : कोड देखने के लिए आपको फ़ंक्शन में देखना होगा
आम तौर पर उपयोगी होने के बावजूद, यह फ़ंक्शन बेहद सटीक भी है, क्योंकि यह एक लीप वर्ष फ़ंक्शन का उपयोग करता है। यह स्वभाव से गैर-नियतात्मक है इसलिए इसे कभी भी स्थायी डेटा के रूप में संग्रहीत नहीं किया जाना चाहिए।
टेबल कॉलम उदाहरण
परिकलित स्तंभों को या तो स्थायी (डेटा होने पर परिवर्तन) या गैर-निरंतर (पंक्ति के चयनित होने पर हर बार परिकलित) के रूप में जोड़ा जा सकता है। हम उन दो तरीकों को देख सकते हैं जिनका उपयोग हमने यहां अपनी सामग्री प्रबंधन प्रणाली में किया है।
नोट : स्थायी डेटा प्राप्त करना कठिन हो सकता है क्योंकि इसे पूरा करने के लिए बाधाओं के एक सेट की आवश्यकता होती है
गैर-निरंतर: आयु
ऊपर दिए गए आयु फ़ंक्शन का उपयोग करके, हम इसे एक तालिका में जोड़ सकते हैं और अन्य कॉलम से मानों को पास कर सकते हैं। फिर हम इसे केवल एक कॉलम के रूप में चुनते हैं।
Add to a table
CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)Select Statement
SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]गैर-निरंतर: आयु
लाभ : लगातार, मॉड्यूलर
नुकसान : जरूरत न होने पर क्वेरी की गति धीमी कर देता है।
कायम: छोटा सीएसएस
हमारे पास एक ऐसा फ़ंक्शन है जो CSS के लिए आवश्यक स्थान को 30% तक कम कर देता है। इसे नियमित रूप से कॉल करने से तालिका की चुनिंदा गति धीमी हो जाएगी, और जैसा कि डेटा को शायद ही कभी अपडेट किया जाता है, यह सम्मिलित/अद्यतन समय पर गणना करने के लिए समझ में आता है। कॉलम को एक फ़ंक्शन के रूप में बनाकर, हमें इन ऑपरेशनों को ट्रिगर के रूप में भी करने की कोई आवश्यकता नहीं है।
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)कायम: छोटा सीएसएस
इसे सामान्य कॉलम की तरह ही चुना जा सकता है, और डेटा तालिका में संग्रहीत किया जाता है। यह हमारे कोड को ब्लोटिंग करने वाले बड़े पैमाने पर प्रतिस्थापन कथन के उपयोग से भी बचाता है।
लाभ : लगातार, मॉड्यूलर, तेज चयन गति, ट्रिगर की कोई आवश्यकता नहीं है!
नुकसान : तालिका के लिए आवश्यक स्थान बढ़ाता है, डालने की गति को धीमा करता है
एक दृश्य को बदलना
हम विचारों का उपयोग नहीं करते हैं, इसके अलावा जब हम नियमित रूप से एक ही जॉइन को कई स्थानों पर उपयोग कर रहे होते हैं।
इन उदाहरणों में भी, कोई कारण नहीं है कि तालिका फ़ंक्शन का अधिक प्रभावी ढंग से उपयोग नहीं किया जा सकता है। हमारे द्वारा उपयोग की गई तालिका नीचे दिए गए लिंक पर पाई जा सकती है, और हमारे पास उपयोग के दो उदाहरण हैं, एक फ़ंक्शन के माध्यम से और दूसरा दृश्य का उपयोग करके।
Create a function
CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGOCreate a view
CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunctionUsage
SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0एक दृश्य को बदलना
लाभ : कॉल करने के लिए कॉम्पैक्ट, प्राथमिक कुंजी (आगे जुड़ने के लिए बिल्कुल सही) के साथ लौटाया गया, पैरामीटर पहले कोड में उपयोग किए जा सकते थे।
नुकसान : बनाने के लिए अधिक कोड, कम लचीला
अप्लाई जॉइन में इस्तेमाल करें
टेबल फंक्शन्स अप्लाई जॉइन में उपयोग करने के लिए बहुत अच्छे हैं, क्योंकि डेटा को पंक्ति-दर-पंक्ति के आधार पर पास किया जा सकता है। SQL सर्वर में स्ट्रिंग्स को अलग करने के लिए हम अपने TextToRows फ़ंक्शन का उपयोग करते हैं। नीचे दिए गए उदाहरण में हम अलग-अलग सीमांककों के साथ डेटा को दो बार विभाजित करने के लिए डबल अप्लाई का उपयोग करते हैं।
SQL Code
DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2आगे का विवरण
हमारे द्वारा लिखे गए कुछ कार्यों को नीचे पाया जा सकता है।
