# How to identify Prime numbers in SQL Server

While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment.
```declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
SELECT 2
UNION ALL
SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);
```

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

# Fun with SQL – Find out numbers where adjacent digit differs by 1

Today, I have come through a post by one of my good friends and one of the most famous SQL Server experts in India – Madhivanan.

The question was : “Given a number N, write a code to print all positive numbers less than N in which all adjacent digits differ by 1” Here is my attempt to solve this using SQL Server T-SQL
```--Provide input value
declare @number int
set @number=105

--Create a temporary table to store numbers
Drop table if exists #temp
create table #temp (number int)

;with ctebuilder as(
select
row_number () over (order by (select null)) as sno
from
sys.sysobjects as t1 cross join sys.sysobjects as t2 )
Insert into #temp(number) Select sno From ctebuilder where sno<=@number

;with cte as(
select
t1.number number,
cast(substring(cast(t1.number as varchar(20)),t2.number,1) as int) as number2,
row_number () over(partition by t1.number order by (select null) asc) Rn
from #temp as t1 cross join #temp as t2
where t2.number <= len(t1.number) and t1.number <=@number
)
Select number From(
Select * ,LAG(number2,1) OVER (partition by number
ORDER BY (select NULL) asc
) previous_val From cte
) A group by number
having count(case when previous_Val - number2 in (1,-1) then 1 Else NULL End )
= count(number2)-1
```

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!