एमएस SQL ​​2011 - नया ऑफसेट स्टेटमेंट

नया SQL सर्वर 2011 (Denali) दो उच्च प्रत्याशित अतिरिक्त आदेशों के साथ ऑर्डर बाय कमांड की क्षमताओं का विस्तार करता है:

• ऑफसेट (ऑफसेट)
• पहले लाएं या अगला लें ( पहले लें ... या अगला लें ... )

ओफ़्सेट

इस आदेश का उपयोग करने से आप क्वेरी परिणाम प्रदर्शित करने से पहले निर्दिष्ट संख्या को छोड़ सकते हैं। इसका क्या मतलब है: मान लीजिए कि हमारे पास एक तालिका में 100 रिकॉर्ड हैं और हमें 100 के माध्यम से पहले 10 पंक्तियों और 11 लाइनों को प्रिंट करने की आवश्यकता है। अब यह निम्नलिखित प्रश्न द्वारा आसानी से हल किया गया है:

Select * From <SomeTable> Order by <SomeColumn> Offset 10 Rows 

उन कामरेडों के लिए, जो .Net का अभ्यास करते हैं, स्किप कलेक्शंस के लिए विस्तार विधि, जो निर्दिष्ट संख्याओं को छोड़ देती है, परिचित होनी चाहिए। तो ऑफसेट अभिव्यक्ति ठीक उसी तरह काम करती है। किसी तरह से डेटा का आदेश दिए जाने के बाद, ऑफसेट अभिव्यक्ति को लागू किया जा सकता है।

ऐसी स्थिति जिसमें ऑफसेट अभिव्यक्ति का उपयोग किया जा सकता है

ऑफसेट के बाद के सभी उदाहरणों में, यह इस स्क्रिप्ट के परिणामस्वरूप निर्मित डेटा सेट का उपयोग करेगा:

 --    Declare @tblSample Table ( [Person Name] Varchar(50) ,Age int ,Address Varchar(100) ) --   Insert into @tblSample Select 'Person Name' + CAST(Number AS VARCHAR) , Number ,'Address' + CAST(Number AS VARCHAR) From master..spt_values Where Type = 'p' And Number Between 1 and 50 

कार्य 1. पहले 10 प्रविष्टियों को छोड़ दें और बाकी को दिखाएं।

पटकथा सरल होगी।

 Select * From @tblSample Order by Age Offset 10 Row 

या

 Select * From @tblSample Order by Age Offset (10) Rows 

आउटपुट निम्नानुसार होगा:

 Person Name     Age     Address Person Name11   11     Address11 Person Name12   12      Address12 . . . . . . . . . . . . . . . . . . . .. . . . . . . . . Person Name49   49      Address49 Person Name50   50      Address50 

इससे कोई फर्क नहीं पड़ता कि लाइनों की संख्या को इंगित करने के बाद किस शब्द का उपयोग करना है: पंक्ति या पंक्तियाँ - वे इस मामले में समानार्थी हैं।

कार्य 2. चर के रूप में छोड़ने के लिए लाइनों की संख्या को पास करें

 --       -    Declare @RowSkip As int --      Set @RowSkip = 10 --   Select * From @tblSample Order by Age Offset @RowSkip Row 

कार्य 3. एक अभिव्यक्ति के रूप में छोड़ने के लिए लाइनों की संख्या निर्धारित करें

 --    14  50 Select * From @tblSample Order by Age Offset (select MAX(number)/99999999 from master..spt_values) Rows 

अभिव्यक्ति मास्टर से MAX (संख्या) / 99999999 का चयन करें । 14 नंबर वापस कर देंगे।

कार्य 4. उपयोगकर्ता-निर्धारित फ़ंक्शन के रूप में स्किप करने के लिए लाइनों की संख्या निर्धारित करें।

 Select * From @tblSample Order by Age Offset (select dbo.fn_test()) Rows 

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

 CREATE FUNCTION fn_test() RETURNS int AS BEGIN Declare @ResultVar as int Select @ResultVar = 10 RETURN @ResultVar END GO 

कार्य 5. अंदर के विचारों, कार्यों, उपश्रेणियों, नेस्टेड टेबल, कॉमन टेबल एक्सप्रेशंस (कॉमन टेबल एक्सप्रेशंस - सीटीई) द्वारा ऑर्डर के साथ ऑफसेट का उपयोग करना।

उदाहरण के लिए, सामान्य शब्दों में उपयोग करें।

 ;With Cte As ( Select * From @tblSample Order By Age Offset 10 Rows) Select * From Cte 

नीचे दिए गए उदाहरण नेस्टेड तालिका के अंदर ऑफसेट और ऑर्डर के उपयोग को दर्शाता है।

 Select * From (Select * From @tblSample Where Age >10 Order By Age Offset 10 Rows) As PersonDerivedTable 

और प्रतिनिधित्व के साथ ऑफसेट और ऑर्डर के काम का एक और उदाहरण।

 --  view Create View vwPersonRecord AS Select * FROM tblSample GO --    view Select * From vwPersonRecord Where Age > 10 Order By Age Offset 10 Rows 

जब ऑफसेट काम नहीं करेगा

1. चूंकि यह एक "विस्तार विधि" है, अभिव्यक्ति के बिना कुछ भी काम नहीं करेगा।

 Select * From @tblSample Offset (10) Rows 

त्रुटि की रिपोर्ट करें

एमएसजी 102, लेवल 15, स्टेट 1, लाइन 21 '10' के पास गलत सिंटैक्स।

2. आप ऑफसेट के लिए नकारात्मक मान सेट नहीं कर सकते।

 Select * From @tblSample Order by Age Offset (-10) Rows 

SQL सर्वर इंजन का उत्पादन करेगा

Msg 10742, स्तर 15, राज्य 1, पंक्ति 22 OFFSET क्लॉज में निर्दिष्ट ऑफसेट नकारात्मक नहीं हो सकता है।

3. आप पूर्णांक प्रकार के अलावा अन्य मान निर्दिष्ट नहीं कर सकते।

 Select * From @tblSample Order by Age Offset 10.5 Rows 

या

 Select * From @tblSample Order by Age Offset Null Rows 

हमें देंगे

Msg 10743, स्तर 15, राज्य 1, पंक्ति 24 OFFSET क्लॉज के लिए दी गई पंक्तियों की संख्या एक पूर्णांक होनी चाहिए।

4. ओवर () अभिव्यक्ति के अंदर इस्तेमाल नहीं किया जा सकता है।

 ;With Cte As ( Select *, Rn = Row_Number() Over(Order by Age Offset 10 Rows) From @tblSample ) Select * from Cte 

अनुरोध के निष्पादन के दौरान हमें एक संदेश मिलता है

एमएसजी 102, स्तर 15, राज्य 1, लाइन 22 'ऑफसेट' के पास गलत सिंटैक्स।

फ़ेच प्रथम / फ़ॉच का उपयोग करना

ये कीवर्ड ऑफ़सेट अभिव्यक्ति का उपयोग करके स्ट्रिंग की एक सरणी को छोड़ने के बाद लौटी पंक्तियों की संख्या निर्दिष्ट करने के लिए उपयोग किए जाते हैं। कल्पना कीजिए कि हमारे पास 100 लाइनें हैं और हमें पहले 10 को छोड़ना होगा और अगली 5 लाइनें प्राप्त करनी होंगी। यानी 11 से 15 के लिए लाइनों की जरूरत है।

 Select * From <SomeTable> Order by <SomeColumn> Offset 10 Rows Fetch Next 5 Rows Only; --  Fetch First 5 Rows Only 

ऐसी क्वेरी पंक्तियों की अपेक्षित संख्या लौटाएगी। .Net पर प्रोग्रामर तुरंत टेक एक्सटेंशन विधि को याद करेंगे।

इसके बाद, उन स्थितियों पर विचार करें जहाँ आप इन खोजशब्दों को लागू कर सकते हैं।

कार्य 1. पहले 10 प्रविष्टियां छोड़ें और अगले 5 प्राप्त करें

 Select * From @tblSample Order by Age Offset 10 Row Fetch First 5 Rows Only 

परिणाम इस प्रकार होगा:

 Person Name     Age     Address Person Name11   11      Address11 Person Name12   12      Address12 Person Name13   13      Address13 Person Name14   14      Address14 Person Name15   15      Address15 

कार्य 2. एक चर का उपयोग करके आउटपुट के लिए लाइनों की संख्या निर्धारित करें

 --     Declare @RowSkip As int --    -   Declare @RowFetch As int -- -    Set @RowSkip = 10 -- -    Set @RowFetch = 5 --    11  15 Select * From @tblSample Order by Age Offset @RowSkip Row Fetch Next @RowFetch Rows Only; 

सामान्य तौर पर, इन कीवर्ड के साथ आप ऑफ़सेट के साथ भी वही काम कर सकते हैं। उपश्रेणियाँ, विचार, कार्य आदि।

जब पहली बार प्राप्त करें / अगले काम नहीं करेंगे

इन खोजशब्दों पर प्रतिबंध ऑफसेट पर पूरी तरह से प्रतिबंध के अनुरूप हैं।

ऑफसेट और Fetch अगला सिमुलेशन Sql Server 2005/2008 के लिए

SQL सर्वर के पिछले संस्करणों में, आप Row_Number () रैंकिंग फ़ंक्शन का उपयोग करके समान कार्यक्षमता प्राप्त कर सकते हैं। उदाहरण के लिए, बेशक, कोड इतना सुंदर और संक्षिप्त नहीं था:

 --      Declare @RowSkip As int --    -    Declare @RowFetch As int --   Set @RowSkip = 10 Set @RowFetch = 5 ;With Cte As ( Select rn=ROW_NUMBER() Over(Order by (Select 1) /*    */ ) ,* From @tblSample ) --    11  15 Select [Person Name] ,Age ,Address From Cte --   Offset  Fetch First/Fetch Next Where rn Between (@RowSkip+1) --  Offset And (@RowSkip+ @RowFetch) --  Fetch First/Fetch Next Clause 

CTE के अंदर, एक सर्विस कॉलम जेनरेट किया जा रहा है जो बस लाइनों को संख्या देता है, जिसके बाद लाइनों को इस क्षेत्र द्वारा फ़िल्टर किया जाता है। विधि सबसे तेज़ नहीं है जैसा कि आप समझते हैं।

ऑफसेट और Fetch अगला सिमुलेशन Sql Server 2000 के लिए

इन प्राचीन सर्वरों के लिए कोई रैंकिंग फ़ंक्शन नहीं थे, लेकिन फिर भी चर्चा की गई कार्यक्षमता को दोहराया जा सकता है। फिर, एक ऑटो वृद्धिशील क्षेत्र के साथ अस्थायी तालिकाओं का उपयोग किया गया था। उदाहरण लिपि:

 Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 10 Set @RowFetch = 5 --   ,    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END --   Create Table #Temp ( Rn int Identity ,[Person Name] Varchar(50) ,Age int ,Address Varchar(100) ) --    Insert Into #Temp([Person Name],Age,Address) Select [Person Name],Age,Address From @tblSample --    11  15 Select [Person Name] ,Age ,Address From #Temp --   Offset  Fetch First/Fetch Next Where Rn Between (@RowSkip+1) --  Offset And (@RowSkip+ @RowFetch) --  Fetch First/Fetch Next 

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

समय और संसाधन माप के साथ ऑफसेट और फ़ेच अभ्यास

मुझे यकीन है कि ऑफ़सेट और फ़िच के उपयोग और उद्देश्य के बारे में पिछले सभी स्पष्टीकरण ने आपको स्पष्ट समझ दी है कि उनकी आवश्यकता क्यों है और आप उनका उपयोग कहां कर सकते हैं। मौजूदा कोड को अनुकूलित करने के लिए विचारों का जन्म हुआ। आगे हम वास्तविक अभ्यास से एक उदाहरण पर विचार करेंगे जब ऑफ़सेट उपयोगी हो सकता है। विभिन्न SQL सर्वरों पर प्रदर्शन माप के परिणाम भी प्रस्तुत किए जाएंगे। टेस्ट 1 मिलियन पंक्तियों के नमूने पर चलाए जाएंगे।

सबसे पहले, निम्नलिखित स्क्रिप्ट का उपयोग करके एक खाता तालिका बनाएं।

 --   tblSample,    IF OBJECT_ID('tblSample','U') IS NOT NULL BEGIN DROP TABLE tblSample END GO --   Create Table tblSample ( [Person ID]     Int Identity ,[Person Name] Varchar(100) ,Age Int ,DOB Datetime ,Address Varchar(100) ) GO --     Insert into tblSample Select 'Person Name' + CAST(N AS VARCHAR) , N ,DATEADD(D,N, '1900-01-01') ,'Address' + CAST(N AS VARCHAR) From dbo.tsqlc_Tally Where N Between 1 and 1000000 --   Select * From tblSample 

सर्वर-साइड डेटा ब्राउज़िंग

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

प्रयोगात्मक उद्देश्यों के लिए, हम पहले 20,000 प्रविष्टियों को छोड़ देंगे और अगले 50,000 को ले लेंगे।

SQL Server 2000 के लिए दृष्टिकोण

 --      DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO --    Declare @RowSkip As int Declare @RowFetch As int --     Set @RowSkip = 20000 Set @RowFetch = 50000 --  ,    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN Drop Table #Temp END --    Create Table #Temp ( Rn int Identity ,[Person ID] int ,[Person Name] Varchar(50) ,Age int ,DOB datetime ,Address Varchar(100) ) --      Insert Into #Temp([Person ID],[Person Name],Age,DOB,Address) Select [Person ID],[Person Name],Age,DOB,Address From dbo.tblSample --    20 000  70 000 Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From #Temp --   Offset  Fetch First/Fetch Next Where Rn Between (@RowSkip+1) --  Offset And (@RowSkip+ @RowFetch) --  Fetch First/Fetch Next GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO 

मुझे लगता है कि स्क्रिप्ट को समझने के लिए पिछले उदाहरण और टिप्पणियां पर्याप्त हैं।

लीड समय:

SQL सर्वर निष्पादन समय:
सीपीयू समय = 110 एमएस, बीता हुआ समय = 839 एमएस।

मैं / ओ आँकड़े:
स्कैन गिनती 1,
तार्किक 8037 पढ़ता है,
भौतिक पाठ 0,
आगे पढ़े 0,
लोब तार्किक पढ़ता है 0,
लोब शारीरिक पढ़ें 0,
लोब पढ़ा-लिखा आगे पढ़ता है ०।

SQL Server 2005/2008 के लिए दृष्टिकोण

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 20000 Set @RowFetch = 50000 ;With Cte As ( Select rn=ROW_NUMBER() Over(Order by (Select 1)) ,* From dbo.tblSample ) Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From Cte Where rn Between (@RowSkip+1) <em> </em> And (@RowSkip+ @RowFetch) GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO 

लीड समय:

SQL सर्वर निष्पादन समय:
सीपीयू समय = 78 एमएस, बीता हुआ समय = 631 एमएस।

मैं / ओ आँकड़े:
स्कैन गिनती 1,
तार्किक पढ़ता है 530,
भौतिक पाठ 0,
आगे पढ़े 1549,
लोब तार्किक पढ़ता है 0,
लोब शारीरिक पढ़ें 0,
लोब पढ़ा-लिखा आगे पढ़ता है ०।

SQL सर्वर 2011 के लिए दृष्टिकोण

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE USE TSQLDB; GO SET STATISTICS IO ON; SET STATISTICS TIME ON; GO Declare @RowSkip As int Declare @RowFetch As int Set @RowSkip = 20000 Set @RowFetch = 50000 Select * From dbo.tblSample Order by (Select 1) Offset @RowSkip Row Fetch Next @RowFetch Rows Only; GO SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO 

लीड समय :

SQL सर्वर निष्पादन समय:
सीपीयू समय = 47 एमएस, बीता हुआ समय = 626 एमएस।

मैं / ओ आँकड़े:
स्कैन गिनती 1,
तार्किक पढ़ता है 530,
भौतिक पाठ 0,
आगे पढ़े 1549,
लोब तार्किक पढ़ता है 0,
लोब शारीरिक पढ़ें 0,
लोब पढ़ा-लिखा आगे पढ़ता है ०।
सबसे दिलचस्प परिणाम प्रोसेसर समय (सीपीयू समय) और रनटाइम (बीतने का समय - निष्पादित करने के लिए अनुरोध द्वारा आवश्यक समय) का उपयोग है। माप की तुलना नीचे प्रस्तुत की गई है:

Sql सर्वर संस्करण	सीपीयू समय	बीता हुआ समय
2000	110ms	839 मि
2005/2008	78ms	631 मि
2011	46ms	626 मि

तालिका स्पष्ट रूप से दिखाती है कि नया SQL सर्वर पिछले संस्करणों की तुलना में बहुत तेज है। स्वाभाविक रूप से, आपकी मशीन के लिए, समय माप भिन्न हो सकते हैं, लेकिन नए सर्वर का प्रदर्शन हमेशा अधिक रहेगा।

शीर्ष अभिव्यक्ति के लिए वैकल्पिक

कुछ स्थितियों में Denali की नई सुविधाएँ TOP की जगह ले सकती हैं।
एक उदाहरण के लिए हम एक स्थिति लेंगे जब किसी भी पैरामीटर के अवरोही क्रम में क्रमबद्ध पहले 10 रिकॉर्ड प्राप्त करना आवश्यक हो।

पिछला संस्करण दृष्टिकोण

 Select Top(10) [Person ID] ,[Person Name] ,Age ,DOB ,Address From dbo.tblSample Order By Age Desc 

SQL सर्वर Denali में संभव दृष्टिकोण

 Select [Person ID] ,[Person Name] ,Age ,DOB ,Address From dbo.tblSample Order By Age Desc Offset 10 Rows 

जैसा कि टिप्पणियों में कहा गया है , यह गलत कोड है और परिणाम को शीर्ष (10) पर वापस लौटा देगा।

चक्र से स्थानांतरण:
MS SQL सर्वर 2011: स्टैंड-अलोन डेटाबेस , नया अनुक्रम ऑब्जेक्ट , ऑफसेट स्टेटमेंट , एरर हैंडलिंग , रिजल्ट सेट निर्माण के साथ , SSMS में नया ।