Error on = in find gap statement

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

Error on = in find gap statement

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2015-03-18 : 12:42:57

I am trying to find gaps in my primary key so I can fill those in and the query I am running is:

[code]
SELECT (t1.qid+1) AS gap_starts_at,
(SELECT MIN(t3.qid) -1 FROM anthemq.dbo.qualmain t3 WHERE t3.qid > t1.qid) AS gap_ends_at
FROM anthemq.dbo.qualmain AS t1
WHERE exists (SELECT t2.qid FROM anthemq.dbo.qualmain t2 WHERE t2.qid = t1.qid + 1) = FALSE
ORDER BY (t1.qid+1);
[/CODE]

The SQL Server code does not like the = sign before false and I have tried playing around but nothing seems to work.

tina m miller

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 12:44:45

WHERE NOT EXISTS (SELECT t2.qid FROM anthemq.dbo.qualmain t2 WHERE t2.qid = t1.qid + 1)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 12:45:03

Filling in gaps sounds like a bad idea and a waste of time.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 12:45:52

You're probably looking for "where not exists (...)"

edit: Sorry Tara - Didn't see you already answered this question

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2015-03-18 : 12:48:00

It is a waste of time. mgmnt wants no gaps. a bit nit picky...primary key is a primary key......where not worked.

how do I show as solved?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-18 : 12:58:04

You edit the subject to indicate it's resolved.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-18 : 13:24:01

Try this:

with cte
  as (select *
            ,row_number() over(order by qid) as rn
        from anthemq.dbo.qualmain
     )
select a.qid+1 as gap_from
      ,b.qid-1 as gap_to
  from cte as a
       left outer join cte as b
                    on b.rn=a.rn+1
 where a.qid+1<b.qid

Kristen
Test

22859 Posts

Posted - 2015-03-18 : 13:43:53

quote:
Originally posted by tinamiller1

mgmnt wants no gaps.

They can't have it, surely?

User A attempts to add a record. ID=1 is assigned
...
User B attempts to add a record - ID=2 is assigned

User A's insert is aborted for some reason (lets assume referential integrity / locking error / simple data validation code in Procedure did a ROLLBACK).

So ... we now have ID=2 successful, and ID=1 unsuccessful. If you use IDENTITY attribute in SQL then your next record will be ID=3 - SQL will never, now, assign ID=1.

Alternatively you can, manually, maintain a list of available ID numbers. That way when ID=1 fails to be assigned another user, later, will be allocated that ID

But ... that route is fraught with danger, and the IDs are not strictly in chronological order (whilst its risky to assume chronological order for IDs anyway, most humans using ID systems do do that - "ORDER BY ID" and so on ...)

So:

1) Management have to live with GAPs
2) Management have to pay a lot of money to have a No Gaps ID assignment system built, maintained and debugged ... and the cost of fixing any side effects (e.g. people do ORDER BY ID and then, years later, discover that there were some IDs allocated out-of-order because of failed ID assignment re-allocated later and all the ORDER BY ID code that crept in has to be found & fixed)

Best to tell management to forget it (or come and post in this thread and get some folk here to provide answers to whatever their real question is for why they need No Gaps)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources