INSERT निर्माण का वाक्य विन्यास काफी तुच्छ लग सकता है क्योंकि
T-SQL मानक केवल डेटा सम्मिलन के संदर्भ में
VALUES कीवर्ड माना जाता है -
INSERT INTO ... VALUES ....एसक्यूएल सर्वर 2008 की रिलीज़ के साथ,
टी-एसक्यूएल सिंटैक्स ने काफी विस्तार किया है, जिससे न केवल सम्मिलित के संदर्भ में, बहु-रेखा
वाल्व निर्माण का उपयोग करना संभव हो गया है।
इस विषय में, विभिन्न विशिष्ट स्थितियों में
VALUES डिज़ाइन का उपयोग करने की तुलनात्मक प्रभावशीलता पर विचार किया जाएगा। प्रत्येक उदाहरण के लिए, प्राप्त परिणामों का एक वस्तुपरक मूल्यांकन देने के लिए, इसकी कार्यान्वयन योजना पर विचार किया जाएगा।
स्पष्टता के लिए, हम परीक्षण डेटा के साथ एक तालिका बनाएंगे और भरेंगे जिसमें क्वार्टर के संदर्भ में छात्रों के बीच औसत स्कोर की जानकारी होगी।
IF OBJECT_ID('dbo.GradePointAverage', 'U') IS NOT NULL DROP TABLE dbo.GradePointAverage GO CREATE TABLE dbo.GradePointAverage ( StudentID INT , I SMALLINT NOT NULL , II SMALLINT NOT NULL , III SMALLINT NOT NULL , IV SMALLINT NOT NULL , CONSTRAINT PK_GradePointAverage PRIMARY KEY (StudentID) ) INSERT INTO dbo.GradePointAverage (StudentID, I, II, III, IV) SELECT sv.number, sv.number % 94, sv.number % 83, sv.number % 72, sv.number % 61 FROM [master].dbo.spt_values sv WHERE sv.type = 'P' AND sv.number BETWEEN 1 AND 2000
मान लीजिए आप प्रत्येक छात्र के लिए न्यूनतम और अधिकतम अंक जानना चाहते हैं।
तुलना को अधिक रोचक बनाने के लिए, प्रत्येक प्रस्तावित दृष्टिकोण अलग-अलग स्थितियों में किया जाएगा: 1) जब तालिका में एक प्राथमिक कुंजी और 2) है जब तालिका एक अनियंत्रित ढेर है।
सबसे पहले, हम सबसे असफल कार्यान्वयन उदाहरण देते हैं:
SELECT StudentID , MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( SELECT StudentID, GradePoint = I FROM dbo.GradePointAverage UNION ALL SELECT StudentID, II FROM dbo.GradePointAverage UNION ALL SELECT StudentID, III FROM dbo.GradePointAverage UNION ALL SELECT StudentID, IV FROM dbo.GradePointAverage ) t GROUP BY StudentID
डेटा को एक बार पढ़ने के बजाय, स्रोत तालिका 4 बार एक्सेस की जाती है - यह है, इसे हल्के ढंग से रखने के लिए, तर्कसंगत नहीं:
आइए
UNPIVOT निर्माण का उपयोग करके बार-बार पढ़ने से छुटकारा पाने की कोशिश करें:
SELECT StudentID , MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( SELECT * FROM dbo.GradePointAverage UNPIVOT ( GradePoint FOR Grade IN (I, II, III, IV) ) unpvt ) t GROUP BY StudentID
बार-बार रीडिंग चली गई, लेकिन योजना और जटिल हो गई:
इसके अलावा, मामले में जब तालिका में क्लस्टर इंडेक्स नहीं होता है, तो सर्वर को डेटा को व्यवस्थित करने के लिए छँटाई का उपयोग करना पड़ता है।
आइए देखें कि
वैल्यू डिजाइन कैसे व्यवहार करता है:
SELECT gpa.StudentID , t.MaxGradePoint , t.MinGradePoint FROM dbo.GradePointAverage gpa CROSS APPLY ( SELECT MaxGradePoint = MAX(GradePoint) , MinGradePoint = MIN(GradePoint) FROM ( VALUES (I), (II), (III), (IV) ) t (GradePoint) ) t
जब स्थितियां बदलती हैं, तो योजना बहुत सरल और अपरिवर्तित रहती है:
SSMS से प्राप्त
क्वेरी लागत मूल्य भी स्पष्ट रूप से
VALUES डिजाइन के लाभों की पुष्टि करता है:
VALUES निर्माण का उपयोग पंक्तियों को स्तंभों में परिवर्तित करने तक सीमित नहीं है। इस डिजाइन का एक और प्रभावी उपयोग स्वरूपित क्वेरी आउटपुट है।
मैं तुरंत स्पष्ट कर दूंगा कि मैं इसे एक अच्छा अभ्यास नहीं मानता, क्योंकि यह कार्य क्लाइंट पर किया जाना चाहिए। फिर भी, ऐसे कार्य अक्सर उपयोगी होते थे, उदाहरण के लिए, रिपोर्ट विकसित करते समय।
प्रत्येक स्रोत पंक्ति के लिए मान लें कि आपको निम्न रूप में डेटा प्रदर्शित करना है:
आप छँटाई के साथ
UNION ALL का उपयोग करके कई बार डेटा पढ़कर इस समस्या को हल कर सकते हैं:
SELECT StudentID , GradePoint , AverageGradePoint FROM ( SELECT ID = StudentID, StudentID, GradePoint = I, AverageGradePoint = (I + II + III + IV) / 4., RN = 1 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, II, NULL, 2 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, III, NULL, 3 FROM dbo.GradePointAverage UNION ALL SELECT StudentID, NULL, IV, NULL, 4 FROM dbo.GradePointAverage ) t ORDER BY ID, RN
फिर से हम बार-बार रीडिंग लेते हैं। इस मामले में, छंटाई पर ध्यान दें, उस स्थिति में जब तालिका में क्लस्टर इंडेक्स नहीं है:
वैकल्पिक रूप से, आप
UNPIVOT निर्माण पर लौट सकते हैं, लाइन नंबर की जाँच करते हुए:
SELECT StudentID = CASE WHEN RN = 1 THEN StudentID END , GradePoint , AverageGradePoint = CASE WHEN RN = 1 THEN AverageGradePoint END FROM ( SELECT StudentID , GradePoint , AverageGradePoint , RN = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY 1/0) FROM ( SELECT *, AverageGradePoint = (I + II + III + IV) / 4. FROM dbo.GradePointAverage ) gpa UNPIVOT ( GradePoint FOR Grade IN (I, II, III, IV) ) unpvt ) t
बार-बार रीडिंग चली गई थी, लेकिन ढेर का उपयोग करते समय छंटाई कहीं भी गायब नहीं हुई:
हम अधिक सुंदर क्वेरी लिखकर
VALUES निर्माण लागू करते हैं:
SELECT t.* FROM dbo.GradePointAverage OUTER APPLY ( VALUES (StudentID, I, (I + II + III + IV) / 4.) , (NULL, II, NULL) , (NULL, III, NULL) , (NULL, IV, NULL) ) t (StudentID, GradePoint, AverageGradePoint)
हमें एक सरल और प्रभावी निष्पादन योजना मिली:
क्वेरी लागत के अनुसार,
VALUES डिज़ाइन एक बार फिर प्रतिद्वंद्वियों की तुलना में अपनी प्रभावशीलता प्रदर्शित करता है:
संक्षिप्त निष्कर्ष:VALUES डिज़ाइन
UNPIVOT के लिए पूर्ण प्रतिस्थापन नहीं है, हालांकि, कुछ स्थितियों में, यह बहुत उपयोगी हो सकता है - आपको प्रश्नों को बहुत सरल बनाने की अनुमति देता है।
मुझे उम्मीद है कि मैं इसे स्पष्ट रूप से प्रदर्शित करने में सक्षम था।