Query question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query question

Author

Topic

Tekno_Guru
Starting Member

2 Posts

Posted - 2007-07-25 : 15:44:08

I would like to create a query that shows numbers that are not in a identity column. So if there are 500,000 records you would hope the values in the identity column would be 1 to 500,000. But if some rows have been removed they identity column might be above 500,000. I would like to figure out what values were removed. I have tried the following queries, but am sure I am not close to what I need. Thank you for any assistance provided.

select identity_column
from example_table
where identity_column
between (1)
and(500000)
and identity_column
not in(
select identity_column
from example_table
where identity_column
between (1)
and (500000)
)

select identity_column
from example_table
where identity_column
between (1)
and(500000)
and not exists(
select identity_column
from example_table
where identity_column
between (1)
and (500000)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 15:59:18

[code]SELECT n.Number
FROM F_TABLE_NUMBER_RANGE(1, 500000) AS n
LEFT JOIN YourTableNameHere AS x ON x.PrimaryColHere = n.Number
WHERE x.PrimaryColHere IS NULL[/code]

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:02:17

[code]SELECT s1.RowNum + 1 AS MissingID
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.RowNum - 1 = s1.RowNum
WHERE s2.RowNum IS NULL
AND s1.RowNum < (SELECT MAX(RowNum) FROM @Sample)[/code]This will only work if the "gaps" are maximum 1 missing id (3, 4, 5, 6)

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:02:43

Moderator, move that post!

E 12°55'05.76"
N 56°04'39.42"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 16:37:25

quote:
Originally posted by Peso

Moderator, move that post!

E 12°55'05.76"
N 56°04'39.42"

Not sure if the question is 2000 or 2005, so I chose 2005 for the forum.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Tekno_Guru
Starting Member

2 Posts

Posted - 2007-07-27 : 00:02:09

Hello Peso,
I tried your second query and it eventually just timed out. The first one calls a function that I don't have on my server/database. Any other ideas? Is my intent clear? Thanks again for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 04:28:02

THe F_TABLE_NUMBER_RANGE function can be found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources