A Basic Code for Seat Reservation Algorithm

I receive this question from the forum that I frequently visit. Given a list of availabe seat number, I need to return the first available free seats based on the required number seats.

Say the following are the available seats:
1, 2, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

Say you need 1 seat, the first available seat is 1.
Say you need 2 seats, the first available seats are 1-2.
Say you need 3 seats, the first available seats are 5, 6, 7
Say you need 4 seats, the first available seats are 5, 6, 7, 8
Say you need 5 seats, the first available seats are 10, 11, 12, 13, 14
Although there are a number of ways to return the available seats, I believe some the processing has to be done on the front-end than just leaving everything on the back-end. Here is my recommended query:

declare @seat table (seatnum smallint)
declare @NumberOfSeatsNeeded smallint

insert into @seat (seatnum) values(1);
insert into @seat (seatnum) values( 2);
insert into @seat (seatnum) values( 5);
insert into @seat (seatnum) values( 6);
insert into @seat (seatnum) values( 7);
insert into @seat (seatnum) values( 8);
insert into @seat (seatnum) values( 10);
insert into @seat (seatnum) values( 11);
insert into @seat (seatnum) values( 12);
insert into @seat (seatnum) values( 13);
insert into @seat (seatnum) values( 14);
insert into @seat (seatnum) values( 15);
insert into @seat (seatnum) values( 16);
insert into @seat (seatnum) values( 17);
insert into @seat (seatnum) values( 18);
insert into @seat (seatnum) values( 19);
insert into @seat (seatnum) values( 20)

set @NumberOfSeatsNeeded = 3

select startseat, endseat,
(endseat - startseat) + 1 as numberofseats
from
(select
startseat =
(select top 1 a.seatnum
  from @seat a
left join @seat b on a.seatnum = b.seatnum + 1
where
   b.seatnum + 1 is null and x1.seatnum > a.seatnum
 order by 1 desc), 
x1.seatnum as endseat
from
(select x.seatnum
from @seat x
left join @seat y on x.seatnum = y.seatnum + 1
where y.seatnum + 1 is not null) x1
union all
select a.seatnum as startseat, b.seatnum as endseat
from @seat a
inner join @seat b on a.seatnum = b.seatnum) vacantseats
where(endseat - startseat) + 1 = @NumberOfSeatsNeeded
order by 1, 3

As I mentioned, there are other ways of doing this. Improvements are always welcome...

~~ CK

How to Find my Missing Identity

IDENTITY is a property of a numeric data type that tells SQL Server it is an auto-incrementing column. It increments every time a new record is inserted to the table.

An identity column can not start with value less than the seed and will always increment by the identity increment property. Although an identity column may be unique, it is not always sequential. If a record is deleted from the table, the deleted value will not be reuse, even if it's the last record.

Because it's not always sequential, it's possible that there are missing value on the column that breaks the sequence. Here's a query that could check for the missing sequence in an identity column:

select a.IdentityColumn+1
from TableName A
left join TableName B on A.IdentityColumn+1 = B.IdentityColumn
where B.IdentityColumn is null

~~CK

The Problem with IN

In one of my old notes, I wrote how JOIN, IN and EXISTS are all affected by NULL values in the columns used to join tables, or to check for existence of relationship (parent-child) between tables.

Here are some queries that use IN that will not give any syntax error but could give unexpected results.

Consider this sample table:

select * from Authors
select * from books

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

aid         BookID      BookTitle                      BookPrice
----------- ----------- ------------------------------ ---------------------
1           1           Yesterday                      5.00
1           2           In My Life                     7.50
1           3           Hey Junde                      4.45
2           4           Fool On the Hill               NULL
2           6           If I Fell                      7.80
4           7           Let It Be                      NULL
4           8           Till There Was You             0.00
2           9           Yellow Submarine               34.65
1           10          I Should Have Known Better     65.33

Now, I executed this query:

select *
from authors
where  authorid in
   (select Authorid from Books 
        where BookTitle = 'Yesterday')

select *
from authors
where authorid in
   (select aid from Books
      where BookTitle = 'Yesterday')

Here are the result sets:

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco
2           Paul                           Los Angeles
3           George                         San Diego
4           Ringo                          Oakland

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco

Just like what I saw with EXISTS in my old notes, the parser did not return an error on the first query, but returned an unexpected result instead. Separately, the subquery will return an error.

select Authorid from Books
where BookTitle = 'Yesterday'

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Authorid'.

Just like EXISTS, the first query did not return an error because even though AuthorID is not in Books table, because it exists in Authors table.

Unlike EXISTS, the relationship was established without the need to relate the relationship keys. To ensure accurate results, use table alias.

select *
from authors a
where  authorid in
(select b.Authorid from Books b
    where b.BookTitle = 'Yesterday')

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Authorid'.

select *
from authors a
where authorid in
(select b.aid from Books b
  where b.BookTitle = 'Yesterday')

AuthorID    AuthorName                     AuthorCity
----------- ------------------------------ ---------------
1           Jhon                           San Francisco

~~CK