Missing Sequence Number in SQL | Recursive CTE | Generate Sequence Number | SQL Interview Q&A

Published: 16 January 2021
on channel: SQL With RaviMartha

6,742

103

;WITH Emp_CTE (ID) AS
(
SELECT 1 -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
)
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0) -- MAXRECURSION Query Hint

;WITH Emp_CTE (ID) AS
(
SELECT 1 -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
)
SELECT * FROM Emp_CTE

-- PART 2

CREATE TABLE tblEmployee1 (EmpID INT)

INSERT tblEmployee1 values (51000),(51001),(51004),(51009),(51013),(51017),(51020),(51025),(51026)

SELECT * FROM tblEmployee1

DECLARE @StartID INT, @EndID INT;
SELECT @StartID = MIN(EmpID) FROM tblEmployee1
SELECT @EndID = MAX(EmpID) FROM tblEmployee1

;WITH Emp_CTE (ID) AS
(
SELECT @StartID
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) @EndID
)
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee1 E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0)

*/
V22: Comma Separated values to multiple rows | string_split() | SQL Interview Q&A | CROSS APPLY
   • Comma Separated values to multiple ro...

V21: Comma Separated values in SQL | XML PATH | SQL to XML Format | Row Tag| Root Tag | SQL Interview Q&A
   • Comma Separated values in SQL | XML P...

This channel would provide new videos on SQL, ETL and Data warehouse concepts. I would create a separate play list for SQL Tutorials for beginners, advanced concepts, Interview questions and data warehousing concepts

Feel free to drop me an email at sql.ravimartha@gmail.com in case of any queries you have in SQL or clarifications on the videos I have done :)

Blog: https://sqlwithravimartha.blogspot.co...

Video 12: Import Tables from one Server to another Using SSMS | Import tables using SQL Query in SSMS --*   • Import Tables from one Server to anot...
Blog: https://sqlwithravimartha.blogspot.co...

Video 11: Table Variables in SQL Server | Pound Tables | Table Variable in SQL | Table Variable in Tempdb --*   • Table Variables in SQL Server | Pound...
Blog: https://sqlwithravimartha.blogspot.co...

Video 10: Quick Practical onTemporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table--*   • Quick Practical on Temporary tables i...
Blog: https://sqlwithravimartha.blogspot.co...

Video 9: Temporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table -- *   • Temporary tables in SQL Server | Temp...
Blog: https://sqlwithravimartha.blogspot.co...

Video 8: Running total & Avg in SQL | Cumulative Sum & Avg in SQL | Calculating running total & Avg in SQL -- *   • Running total & Avg in SQL | Cumulati...
https://sqlwithravimartha.blogspot.co...

Video 7 Title: Length Vs DataLength Vs ColumnLength | Len() | DataLength() | Col_Length | Column length in SQL -- *    • Length Vs DataLength Vs ColumnLength ...
https://sqlwithravimartha.blogspot.co...

Video 6 Title: Tricky CASE Statement Interview Questions | CASE WHEN | WHEN Clause | CASE Statement in SQL -- *    • Tricky CASE Statement Interview Quest...
https://sqlwithravimartha.blogspot.co...

Video 5 Title: Delete Duplicates in SQL by retaining one Unique record | Row Duplicates | Business Key Duplicates -- *    • Delete Duplicates in SQL by retaining...
https://sqlwithravimartha.blogspot.co...

Video 4 Title: Difference between TRUNCATE, DELETE and DROP -- *    • Difference between TRUNCATE, DELETE a...
https://sqlwithravimartha.blogspot.co...

Video 3 Title: Sorting in SQL | Order By | Conditional Sorting| Unusual Sorting -- *   • Sorting in SQL | Order By | Condition...
https://sqlwithravimartha.blogspot.co...

Video 2 Title: Difference between Primary Key and Unique Key -- *   • Difference between Primary Key and Un...
https://sqlwithravimartha.blogspot.co...

Video 1 Title: Logical Processing of SELECT Statement -- *   • Logical Processing of SELECT Statemen...
https://sqlwithravimartha.blogspot.co...

Blog: https://sqlwithravimartha.blogspot.com/

Facebook Page:
  / sql-with-ravimartha-109497027391239
  / etl-dwh-testing-100494175057349

Youtube Channel:
   / @sqlwithravimartha6357

On this page of the site you can watch the video online Missing Sequence Number in SQL | Recursive CTE | Generate Sequence Number | SQL Interview Q&A with a duration of hours minute second in good quality, which was uploaded by the user SQL With RaviMartha 16 January 2021, share the link with friends and acquaintances, this video has already been watched 6,742 times on youtube and it was liked by 103 viewers. Enjoy your viewing!

643