How to delete referential records from a sql table?

If there is a foreign key relationship between two table then you can not delete a record which is referenced by another table.

Solution:
Use On DELETE CASCADE

Example:
Alter Table Employee
DROP Constraint FK_Employee_Department
GO
ALTER Table Employee
Add Constraint FK_Employee_Department Foreign key (DeptID)
References Department(id)
On DElete Cascade


Note: If you delete a record from department table then all the associated records from Employee will be deleted.

How to find missing Identity id from a sql table?

Below is the sql statement to find the missing identity Id from sql table '#tmp1'
'#tmp1' content

ID	Name
1	Arvind
2	Kapil
5	Mohit
8	Rakesh

SQL Statement
With missing as
(
Select 1 as mins, max(Id) as maxs from #tmp1
UNION ALL
Select mins + 1 , maxs from missing m
--Inner join #tmp1 t ON t.ID = m.mins
where mins < maxs
)
Select * from missing m
left outer join #tmp1 t ON t.ID = m.mins
where t.Id is null

Missing Identity

ID	Name
3	Raj
4	Mangesh
6	Rajesh
7	Ravindra