Special Script

To Display alternatives of column in table when rows are same:

If table contains following data as show below






















Then you want to display alternatively like martin,jack....etc.


SELECT testname
, ROW_NUMBER() OVER (PARTITION BY testname ORDER BY testname) ordby
FROM testoutput
ORDER BY 2,1

To Display Missing numbers/Missing Data in Tables:

If your tables are shown as see below.

Then If you want to find Missing nos of identity column in table of employee_test
see below

WITH DeletedRows (missnum, maxid)
AS
( 
SELECT 1 AS missnum, (select max(Emp_id) from employee_test) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows 
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN employee_test e on e.Emp_id = DeletedRows.missnum
WHERE e.Emp_id is NULL OPTION (MAXRECURSION 0)

Verify on tables as see below: