بدست آوردن id آی دی رکوردهای حذف شده جدولها در sql server

آی دی های حذف شده در sql

توسط admin | گروه SQL Server | 1399/07/25

نظرات 0

 در این نوشته آموزشی که در مورد نکات مفید پایگاه داده SQL Server می باشد ، به ارائه و بررسی یک پرس و جوی اس کیو ال می پردازم که توسط آن می توانید در جدولهایی که دارای ستون شمارنده آی دی هستند آی دیهای حذف شده را بدست آورید. به عنوان مثال اگر آی دی های 1 و 3 و 4 و 5 و 8 و 9 و 10 را در نظر بگیریم آی دی های حذف شده شامل 2 و 6 و 7 خواهند بود. مزیت این پرس و جوی ارائه شده این است یک سری اطلاعات اضافی هم به کاربر نمایش می دهد. از جمله اینکه آی دی بعدی و قبلی آی دی حذف شده کدام است.

بدست آوردن آی دی های حذف شده بین سایر آی دی ها در جدول های SQL Server

چند روز قبل ، در حال کار بر روی یک پروسیجر بودم که متوجه شدم که تعدادی از رکوردها حذف شده اند و به اصطلاح حالت شمارنده آنها ناقص است و این مساله در سیستمهایی مانند حسابداری و انبارداری و خرید و فروش بسیار مهم است. بلافاصله شروع به جستجو کردم و پرسو جوی زیر را پیدا کردم و از آن استفاده نمودم البته کمی هم تغییرات در آن اعمال کردم و به اصطلاح کمی استفاده از برنامه را راحت تر کردم. در ذیل همین نوشته ، اسکریپت کامل برای یافتن آی دی های حذف شده در SQL Server ارائه شده است. برای سادگی کار یک جدول فرضی ایجاد شده و یک سری دیتا داخل آن ریخته می شود و پرس و جو را بر روی آن اجرا می نماییم.
 
اسکریپت ایجاد جدول و درج دیتای تستی درون آن
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblStudentNames](
[STRowID] [bigint] IDENTITY(1,1) NOT NULL,
[StNames] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_tblStudentNames] PRIMARY KEY CLUSTERED 
(
[STRowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
 
SET IDENTITY_INSERT [dbo].[tblStudentNames] ON
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (1, N'Hamid Hamidi')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (2, N'Reza Rezaii')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (4, N'Hoong Toong Moong')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (5, N'Siee Choooa Khooong')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (6, N'Ching Chang Choong')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (11, N'Gambar Tambar')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (12, N'Hagi Dozde Male Mardom Khor')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (13, N'Saman Bee Saman')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (16, N'Jalal Malal')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (17, N'Balal Khoshtip')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (19, N'Saeed Javad Mohammad Masood Zeidan')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (20, N'Amer Gamer')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (22, N'Antonio Root Goolit')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (24, N'Ming Sing Ting Looo')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (25, N'Iman Bee Eman')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (28, N'Jim Jaam')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (29, N'Sara Mara')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (31, N'Ebruu Tibruu')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (35, N'Snag Hang Foo')
INSERT [dbo].[tblStudentNames] ([STRowID], [StNames]) VALUES (37, N'Fati Gati')
SET IDENTITY_INSERT [dbo].[tblStudentNames] OFF
GO 
 
 
اسکریپت جستجوی اعداد و آی دی های حذف شده در میان سایر آی دی ها در جدولهای پایگاه داده
/*Step 1: Create one temp table and store all distinct Numbers*/
DROP TABLE #DistinctID
go
drop table #MissingID
go
drop table  #NextIDTableTable
go
CREATE TABLE #MissingID
(
TableCountID INTEGER
,ID INTEGER
,NextID INTEGER
,MissingID INTEGER
,TotalDiffDeletedIDs INTEGER
)
GO
SELECT DISTINCT STRowID as ID INTO #DistinctID FROM TimeSheetDB.dbo.tblStudentNames
GO
 
/*Step 2: Find NextID which has something missing before it.
Store into one TempTable*/
 
SELECT 
(ROW_NUMBER()OVER (ORDER BY ID ASC)) AS TableCountID
,b.ID 
,(
SELECT Top 1 ID
FROM #DistinctID a WITH (NOLOCK)
WHERE a.ID > b.ID
) AS NextID
INTO #NextIDTableTable
FROM #DistinctID AS b WITH (NOLOCK)
WHERE 
(
SELECT Top 1 ID
FROM #DistinctID a WITH (NOLOCK)
WHERE a.ID > b.ID
) <> b.ID+1
ORDER BY b.ID
 

 
/*Step 4: Now start to find missing id using WHILE Loop. */
 
DECLARE @CountID INTEGER
DECLARE @InnerLoopCountID INTEGER
SET @CountID = 1
WHILE (@CountID > 0)
BEGIN
INSERT INTO #MissingID
SELECT 
TableCountID
,ID 
,NextID
,CASE WHEN TotalDiff = 1 THEN ID+TotalDiff END AS MissingID
,TotalDiff
FROM
(
SELECT
TableCountID
,ID
,NextID
,(NextID-ID)-1 AS TotalDiff
FROM #NextIDTableTable
) AS T WHERE TableCountID = @CountID
 
IF ((SELECT (NextID-ID)-1 FROM #NextIDTableTable WHERE TableCountID = @CountID)>1)
BEGIN
SET @InnerLoopCountID = (SELECT (NextID-ID)-1 FROM #NextIDTableTable WHERE TableCountID = @CountID)
WHILE (@InnerLoopCountID > 0)
BEGIN
INSERT INTO #MissingID
SELECT 
TableCountID
,ID 
,NextID
,ID+@InnerLoopCountID AS MissingNumber
,TotalDiff
FROM
(
SELECT
TableCountID
,ID
,NextID
,(NextID-ID)-1 AS TotalDiff
FROM #NextIDTableTable
) AS T WHERE TableCountID = @CountID
 
SET @InnerLoopCountID = @InnerLoopCountID - 1
END
END
SET @CountID = @CountID+1
 
IF @CountID = (SELECT COUNT(1)+1 FROM #NextIDTableTable)
BEGIN
RETURN
END
END
GO
 
/*Step 5: Now select missing data from #MissingID temp table. 
You can find full details on MissingID.*/
 
SELECT * FROM #MissingID WHERE MissingID IS NOT NULL ORDER BY MissingID 
GO
DROP TABLE #DistinctID
go
drop table #MissingID
go
drop table  #NextIDTableTable
go
 
 
 
 
 

 

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر

اطلاعات تماس

  • آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
  • آدرس ایمیل:najafzade@gmail.com
  • وب سایت:http://www.a00b.com/
  • تلفن ثابت:(+98)9131253620
  • تلفن همراه:09131253620