Skipped PK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Skipped PK

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-07-06 : 14:38:46

Folks,

I've been going through the forums and see some queries that are similar but don't quite get what I'm looking for.

I'd like to be able to see if a record was skipped or deleted by auditing the primary key of a table. I'm not sure if the rank function would work for this.

Basically, consider the following sample data:

Table 1

ID (Primary Key, Auto, Int), Lastname

1, Jones
2, Williams
3, Johnson
5, Salazar
6, Davis
8, Dunn

Notice that there is no ID 4 or 7. This would indicate it was a deleted record.

Is there a query that could search for records that don't follow a consecutive order?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 14:41:19

May I ask why this is important?
Also, which version of SQL Server are you using?

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-06 : 15:29:43

There are a bunch of ways. Here is a nice article by Peso:
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

Also, realize that a gap in the sequence isn't always a deleted row, FYI..

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 15:57:26

quote:
Originally posted by Lamprey

There are a bunch of ways. Here is a nice article by Peso:
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

Also, realize that a gap in the sequence isn't always a deleted row, FYI..

Except for EXCEPT if s/he is not 2k8

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 16:17:33

it's always handy to have a numbers table...probably the first trick I learned here at SQLTeam


DECLARE @myTable99 table (id int, [name] varchar(50))
DECLARE @n table (n int IDENTITY(1,1), x char)
DECLARE @y int

SET @y = 0

WHILE @y < 10
  BEGIN
	INSERT INTO @n(x) SELECT 'x'
	SET @y = @y + 1
  END

SELECT * FROM @n


INSERT INTO @myTable99(id, [name])
SELECT 1, 'Jones'		UNION ALL
SELECT 2, 'Williams'	UNION ALL
SELECT 3, 'Johnson'		UNION ALL
SELECT 5, 'Salazar'		UNION ALL
SELECT 6, 'Davis'		UNION ALL
SELECT 8, 'Dunn'

SELECT * FROM @myTable99

SELECT a.n
  FROM @n a LEFT JOIN @myTable99 b
    ON a.n = b.id
 WHERE b.id IS NULL
   AND a.n < (SELECT MAX(id) FROM @myTable99)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-07-06 : 16:45:28

Thanks guys.

Peso, it is important because the GUI we use does not allow us to prevent users from deleting certain records without limiting other essential work processes. This way, I could quickly screen for "skips" in records.

Lamprey, yes, I realize that it doesn't have to be a deletion; just likely. This would just be a troubleshooting tool.

We're running SQL Server 2005. And I can't use the create table function unless I get onto the SQL Server itself (I have a report writing window that I use that allows me to write SELECT queries only).

Anyway, just a thought...I appreciate everyone's input!

Craig

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 16:48:47

Well, as Lamprey wrote, there are other reasons for an IDENTITY to be missing.
If an insert fails, the identity value is not reused.

N 56°04'39.26"
E 12°55'05.63"

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-07-06 : 17:26:30

Thanks Peso...yeah...I realize that this can happen...I still think it would be helpful know.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources