sql - एसक्यूएल सर्वर में रिकॉर्ड्स हटाने के बाद पहचान बीज रीसेट करें




sql-server database (12)

मैंने SQL सर्वर डेटाबेस तालिका में रिकॉर्ड्स डाले हैं। तालिका में प्राथमिक कुंजी परिभाषित थी और ऑटो वृद्धि पहचान बीज "हां" पर सेट किया गया था। यह मुख्य रूप से किया जाता है क्योंकि एसक्यूएल एज़ूर में, प्रत्येक तालिका में प्राथमिक कुंजी और पहचान परिभाषित होती है।

लेकिन चूंकि मुझे टेबल से कुछ रिकॉर्ड हटाना है, इसलिए उन तालिकाओं के लिए पहचान बीज परेशान हो जाएगा और इंडेक्स कॉलम (जो 1 की वृद्धि के साथ स्वतः उत्पन्न होता है) परेशान हो जाएगा।

रिकॉर्ड्स को हटाए जाने के बाद मैं पहचान कॉलम को रीसेट कैसे कर सकता हूं ताकि कॉलम में आरोही संख्यात्मक क्रम में अनुक्रम हो?

पहचान कॉलम को डेटाबेस में कहीं भी विदेशी कुंजी के रूप में उपयोग नहीं किया जाता है।


@jacob

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

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


इस संग्रहीत प्रक्रिया का प्रयोग करें:

IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
  BEGIN
    EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
  END
GO

SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pResetIdentityField]
  @pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max   INT;
DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;

DECLARE @identityColumn   NVARCHAR(1000);

SELECT @identityColumn = c.[name]
FROM sys.tables t
     INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
     INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE     c.is_identity = 1
      AND t.name = @pTableName
      AND s.[name] = @pSchemaName

IF @identityColumn IS NULL
  BEGIN
    RAISERROR(
      'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
    , 16
    , 1);
    RETURN;
  END;

DECLARE @sqlString   NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;

EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT

IF @max IS NULL
  SET @max = 0

print(@max)

DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go

--exec pResetIdentityField 'dbo', 'Table'

ट्रंकेट टेबल को प्राथमिकता दी जाती है क्योंकि यह रिकॉर्ड्स को साफ़ करता है, काउंटर को रीसेट करता है और डिस स्पेस को पुनः प्राप्त करता है।

हटाएं और चेकइडेंट का उपयोग केवल तभी किया जाना चाहिए जहां विदेशी कुंजी आपको छंटनी से रोकती हैं


पहचान आईडी को नई आईडी के साथ रीसेट करें ...

DECLARE @MAX INT
SELECT @MAX=MAX(ISNULL(Id,0)) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

पहचान कॉलम को रीसेट करने के लिए इस स्क्रिप्ट को चलाएं। आपको दो बदलाव करने की आवश्यकता होगी। TableXYZ को किसी भी तालिका के साथ बदलें जिसे आप अपडेट करना चाहते हैं। साथ ही, पहचान कॉलम का नाम अस्थायी तालिका से हटा दिया जाना चाहिए। यह 35,000 पंक्तियों और 3 कॉलम वाली तालिका पर तात्कालिक था। जाहिर है, तालिका का बैकअप लें और पहले परीक्षण वातावरण में इसे आज़माएं।

select * 
into #temp
From tableXYZ

set identity_insert tableXYZ ON

truncate table tableXYZ

alter table #temp drop column (nameOfIdentityColumn)

set identity_insert tableXYZ OFF

insert into tableXYZ
select * from #temp

पहला: पहचान विशिष्टता बस: "नहीं" >> डेटाबेस निष्पादन परियोजना सहेजें

उसके बाद: पहचान विशिष्टता बस: "हाँ" >> डेटाबेस निष्पादन परियोजना सहेजें

आपका डाटाबेस आईडी, पीके 1 से शुरू करें >>


यह एक आम सवाल है और जवाब हमेशा एक जैसा है: ऐसा मत करो। पहचान मूल्यों को मनमाने ढंग से माना जाना चाहिए और, इस तरह, कोई "सही" आदेश नहीं है।


यह ध्यान दिया जाना चाहिए कि यदि डेटा को सभी डेटा को DELETE (यानी कोई WHERE क्लॉज) के माध्यम से हटाया जा रहा है, तब तक जब तक ए) अनुमतियां इसके लिए अनुमति दें, और बी) तालिका का संदर्भ देने वाले कोई भी एफके नहीं हैं (जो दिखाई देता है यहां मामला होने के लिए), TRUNCATE TABLE का उपयोग करके प्राथमिकता दी जाएगी क्योंकि यह एक अधिक कुशल DELETE करता है और एक ही समय में IDENTITY बीज को रीसेट करता है। TRUNCATE तालिका के लिए एमएसडीएन पृष्ठ से निम्नलिखित विवरण लिया गया है:

DELETE कथन की तुलना में, TRUNCATE तालिका में निम्नलिखित फायदे हैं:

  • कम लेनदेन लॉग स्थान का उपयोग किया जाता है।

    DELETE कथन एक समय में पंक्तियों को हटा देता है और प्रत्येक हटाई गई पंक्ति के लिए लेनदेन लॉग में एक प्रविष्टि रिकॉर्ड करता है। ट्रंकेट टेबल तालिका डेटा को संग्रहीत करने के लिए उपयोग किए गए डेटा पृष्ठों को हटाकर डेटा को हटा देता है और लेनदेन लॉग में केवल पृष्ठ विलोपन रिकॉर्ड करता है।

  • कम ताले आमतौर पर उपयोग किया जाता है।

    जब एक पंक्ति लॉक का उपयोग कर DELETE कथन निष्पादित किया जाता है, तो तालिका में प्रत्येक पंक्ति को हटाने के लिए बंद कर दिया जाता है। ट्रंकेट टेबल हमेशा तालिका को लॉक करता है (स्कीमा (SCH-M) लॉक सहित) और पृष्ठ लेकिन प्रत्येक पंक्ति नहीं।

  • अपवाद के बिना, तालिका में शून्य पृष्ठ छोड़े गए हैं।

    एक DELETE कथन निष्पादित करने के बाद, तालिका में अभी भी खाली पृष्ठ हो सकते हैं। उदाहरण के लिए, एक ढेर में खाली पृष्ठ कम से कम एक विशेष (LCK_M_X) तालिका लॉक के बिना हटाया नहीं जा सकता है। यदि डिलीट ऑपरेशन टेबल लॉक का उपयोग नहीं करता है, तो तालिका (ढेर) में कई खाली पृष्ठ होंगे। इंडेक्स के लिए, डिलीट ऑपरेशन खाली पृष्ठों को पीछे छोड़ सकता है, हालांकि इन पृष्ठों को पृष्ठभूमि क्लीनअप प्रक्रिया द्वारा जल्दी से हटा दिया जाएगा।

यदि तालिका में पहचान कॉलम है, तो उस कॉलम का काउंटर कॉलम के लिए परिभाषित बीज मान पर रीसेट हो जाता है। यदि कोई बीज परिभाषित नहीं किया गया था, तो डिफ़ॉल्ट मान 1 का उपयोग किया जाता है। पहचान काउंटर को बनाए रखने के लिए, इसके बजाय DELETE का उपयोग करें।

तो निम्नलिखित:

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

बस बनता है:

TRUNCATE TABLE [MyTable];

प्रतिबंधों आदि पर अतिरिक्त जानकारी के लिए कृपया TRUNCATE TABLE दस्तावेज (उपरोक्त लिंक) देखें।


हालांकि अधिकांश उत्तर 0 पर RESEED का सुझाव दे रहे हैं, लेकिन कई बार हमें केवल अगले आईडी पर शोध करने की आवश्यकता है

declare @max int
select @max=max([Id])from [TestTable]
if @max IS NUll   //check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

यह तालिका की जांच करेगा और अगली आईडी पर रीसेट करेगा।


हालांकि अधिकांश उत्तर 0 RESEED का सुझाव दे रहे हैं, और कुछ इसे TRUNCATED तालिकाओं के लिए एक दोष के रूप में देखते हैं, माइक्रोसॉफ्ट के पास एक समाधान है जो ID छोड़ देता है

DBCC CHECKIDENT ('[TestTable]', RESEED)

यह तालिका की जांच करेगा और अगली ID रीसेट करेगा। यह एमएस एसक्यूएल 2005 के बाद से वर्तमान में उपलब्ध है।

MSDN


DBCC CHECKIDENT (<TableName>, reseed, 0)

यह वर्तमान पहचान मान 0 पर सेट करेगा।

अगले मूल्य को सम्मिलित करने पर, पहचान मान 1 तक बढ़ जाता है।


DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

जहां 0 identity प्रारंभ मूल्य है





azure-sql-database