December 14, 2010 at 7:02 am
Will tables using identity columns RE-USE identity column values from records which have been deleted?
I know for a fact that records have been deleted from a table and I have used a script to (try to) find the identity values which have been deleted.. see below
select itnumber
from tblInventoryTransactions
where DateTimeStamp >= '1-1-10' -- optional but it is a large table, orginal script used SELECT...Into
-- a temp table
;WITH Missing (missnum, maxid)
AS( SELECT 1416476 AS missnum, (select max(ITNumber) from tblInventoryTransactions
where DateTimeStamp >= '1-1-2010') -- optional selection criteria
UNION ALL SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid)
SELECT missnum
FROM Missing
LEFT OUTER JOIN tblInventoryTransactions it on it.ITNumber = Missing.missnum
WHERE it.ITNumber is NULL
OPTION (MAXRECURSION 0);
After doing a select statement I used the first ITNumber (identity column) as missnum. However it comes showing no numbers missing. This is a SQL Server 2008 R2 system.
Thanx,
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
December 14, 2010 at 7:06 am
Identity values are not reused. Not after a delete, not after a rollback of an insert.
Not sure about that query (and haven't tested). Easiest way would be to take a tally (numbers) table and do a not in/not exists with the main table on the identity column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2010 at 7:09 am
Thank You very much! My code is obviously errant and I'll have to fix it.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
December 14, 2010 at 7:09 am
It looks like you're doing this in a more complicated fashion than you need to.
The easiest way to get missing ID numbers is to compare the IDs to a Numbers table that has the range you want to check against. Anything in the Numbers table that's not in the ID column is missing, so that's a trivially easy query.
The second easiest is to query all IDs, and the Min(ID) which is greater than the ID, in a CTE or derived table, and then use an outer query to pull the ones where the difference is greater than 1.
But it's an academic exercise. Yes, there will be gaps in the ID values if there have been deletions, or even if inserts have been rolled back. So what? It doesn't matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2010 at 7:10 am
See edit to previous post for comment on that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2010 at 7:20 am
Yes, my first attempt was on a temp table of sequential numbers comparing to the live table.
The only reason it matters is because I'm trying find out if a particular person(s) are manually deleting records when they've been told not to.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
December 14, 2010 at 7:24 am
Your second sentence just sunk in with me. I think I had a "duh-huh" moment. Talk about over-complicating things. You're right. This is probably the simplest, yet most reliable way to do it.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
December 14, 2010 at 7:24 am
Caruncles (12/14/2010)
Yes, my first attempt was on a temp table of sequential numbers comparing to the live table.The only reason it matters is because I'm trying find out if a particular person(s) are manually deleting records when they've been told not to.
The better way to do that is to audit the tables. A trace file can be set up to catch that kind of thing, very, very easily. And it's more reliable than simply checking for missing IDs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2010 at 7:32 am
I'm not familiar with setting up a trace file. Will it reveal records (or gaps) which have been deleted via SSMS (or Enterprise Mgr)?
I know I could have our software apps write to a table all the records they updated or deleted, but I know those systems are working and if they delete a record, it's supposed to be deleted. I'm more concerned about manual deletes.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
December 14, 2010 at 7:35 am
Caruncles (12/14/2010)
I'm not familiar with setting up a trace file. Will it reveal records (or gaps) which have been deleted via SSMS (or Enterprise Mgr)?
Depending what you trace, it will reveal all operations against the table, by everyone from all apps. You can filter the trace, or once it's finished you can load it into a table and query it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2010 at 7:41 am
Outstanding! I'll research the trace and play with it. I just started this job 4 months ago and found there is a lot of stuff broken. I appreciate your help! Also appreciate this forum!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply