Tag: T-SQL

Ready reckoner – Date Functions in SQL Server

This blog post tries to provide few of very common scenarios and its solutions that developers would come across in development phase.

Standard Date CONVERT formats

Declare @Date Datetime = GETDATE()
Select @Date
SELECT DATEADD(month, DATEDIFF(month, '', @date), '');

Select CONVERT(Varchar(25),@date,100) as 'Converted Value', 'Mon DD YYYY HH:MIAM (or PM)' as 'Converted Format' union all
Select CONVERT(Varchar(25),@date,1),'MM/DD/YY' union all
Select CONVERT(Varchar(25),@date,101),'MM/DD/YYYY' union all
Select CONVERT(Varchar(25),@date,2),'YY.MM.DD' union all
Select CONVERT(Varchar(25),@date,102),'YYYY.MM.DD' union all
Select CONVERT(Varchar(25),@date,3),'DD/MM/YY' union all
Select CONVERT(Varchar(25),@date,103),'DD/MM/YYYY' union all
Select CONVERT(Varchar(25),@date,4),'DD.MM.YY' union all
Select CONVERT(Varchar(25),@date,104),'DD.MM.YYYY' union all
Select CONVERT(Varchar(25),@date,5),'DD-MM-YY' union all
Select CONVERT(Varchar(25),@date,105),'DD-MM-YYYY' union all
Select CONVERT(Varchar(25),@date,6),'DD Mon YY' union all
Select CONVERT(Varchar(25),@date,106),'DD Mon YYYY' union all
Select CONVERT(Varchar(25),@date,7),'Mon DD, YY' union all
Select CONVERT(Varchar(25),@date,107),'Mon DD, YYYY' union all
Select CONVERT(Varchar(25),@date,108),'HH:MM:SS' union all
Select CONVERT(Varchar(25),@date,109),'Mon DD YYYY HH:MI:SS:MMMAM (or PM)' union all
Select CONVERT(Varchar(25),@date,10),'MM-DD-YY' union all
Select CONVERT(Varchar(25),@date,110),'MM-DD-YYYY' union all
Select CONVERT(Varchar(25),@date,11),'YY/MM/DD' union all
Select CONVERT(Varchar(25),@date,111),'YYYY/MM/DD' union all
Select CONVERT(Varchar(25),@date,12),'YYMMDD' union all
Select CONVERT(Varchar(25),@date,112),'YYYYMMDD' union all
Select CONVERT(Varchar(25),@date,113),'DD Mon YYYY HH:MM:SS:MMM(24h)' union all
Select CONVERT(Varchar(25),@date,114),'HH:MI:SS:MMM(24H)' union all
Select CONVERT(Varchar(25),@date,120),'YYYY-MM-DD HH:MI:SS(24h)' union all
Select CONVERT(Varchar(25),@date,121),'YYYY-MM-DD HH:MI:SS.MMM(24h)' union all
Select CONVERT(Varchar(25),@date,126),'YYYY-MM-DDTHH:MM:SS:MMM' union all
Select CONVERT(nVarchar(25),@date,130),'DD Mon YYYY HH:MI:SS:MMMAM' union all
Select CONVERT(Varchar(25),@date,131),'DD/MM/YYYY HH:MI:SS:MMMAM'

How to separate date from a string

Create table testing(id int identity(1,1), enddate varchar(40))

insert into testing values('End Date: 04/30/2021')
insert into testing values('06/30/2027')
insert into testing values('something something something 06/30/2027')
insert into testing values('13.a')
insert into testing values('Section 23')
insert into testing values('End Date: 06/30/2027')
insert into testing values('End Date: 12/31/2016 a as asdadasd')

;With cte as
(
select ID, enddate,
SUBSTRING(enddate, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', enddate), 10) AS Actualdate
FROM testing
)
select id,enddate,
case when Isdate(right(Actualdate,10)) = 1 then Actualdate
else null end Actualdate from cte

Drop table testing

How to calculate age from a date

create table students(student_id int,DOB date)

Insert into students Values(1,'19790111'),(2,'19790219'),(3,'20200408')

Select student_id,DOB,Cast(getdate() as date),
cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) as years,
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) as ' Months',
cast((DATEDIFF(d, DOB, GETDATE())%365) as varchar) as ' Days'
From students

Drop table students

If you enjoyed this blog post, feel free to share it with your friends!

ROW_NUMBER() window function in SQL Server

This blog post explains few methods to generate a running number for a SELECT query. Different SQL versions have different ways to achieve this. Let us quickly see ROW_NUMBER() window function with this blog post.

ROW_NUMBER()OVER(PARTITION BY column_list ORDER BY column_list ASC/DESC)

This returns the sequential number for rows. A Quite simple way in SQL Server so far, note that there are different ways we can generate this numbers on group or set depending on sorted manner.Let us see some of those forms in this post. As first step, let us create a SampleData table populated with few records as below.

Create Table SampleData
(
	CourseId	Int,
	CourseName	Varchar(100),
	Institute	Varchar(100),
	Fees		Int
)

Insert into SampleData
	Values  (1,'SQL Server', 'Aptech', 1000),
			(1,'SQL Server', 'WowTech', 2000),
			(2,'.NET', 'NetTechs', 6000),
			(2,'.NET', 'Aptech', 8000),
			(2,'.NET', 'SimpleLearn', 7500),
			(3,'Python', 'Aptech', 1000),
			(3,'Python', 'SimpleLearn', 1500),
			(3,'Python', 'PyLearn', 1000),
			(3,'Python', 'NetTechs', 1000),
			(3,'Python', 'WowTech', 1000)

Select * from SampleData

--Drop Table SampleData

Simple Form of generating a running number

We can create a running number column with row_number window function as below:

Select Row_Number()Over(order by (Select NULL) ASC) Rn,* From SampleData
Select Row_Number()Over(order by (Select NULL) DESC) Rn,* From SampleData

In the above two example, we can see both ASC and DESC returns the same set of ordering and running number because the order by always on NULL value which would have no effect for ASC and DESC.

Form of generating a running number based on a set of data

Yes, this is based on set or group data. For an example, if we need to generate a running number for a grouped records and so on, you can introduce the Partition by clause to the above query as below.

Select Row_Number()Over( partition by CourseId order by (Select NULL) ASC) Rn,* From SampleData

Form of generating a running number based on a set of data with a defined order of data

In the above example, we have seen the number based on data, now, we are going to quickly see the numbering is based on group of data as well as ordered in a defined way. In our example, we wanted to see the data group for CourseID should be sorted by its Fees in ascending order.

Select Row_Number()Over( partition by CourseId order by Fees ASC) Rn,* From SampleData

Hope this is helpful to understand better of ROW_NUMBER window function in SQL Server.

Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information.

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

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!

SQL Formatters – Beautify your code and coding experience

Today, I am going to introduce some of tools available for SQL Server formatting.

The list is not in order of any preferences, but just the way it is. Explore and use as your choice.

1. Format SQL – https://format-sql.com/
2. Instant SQL Formatter – http://www.dpriver.com/pp/sqlformat.htm
3. SQL Prompt from redgate – http://www.red-gate.com/products/sql-development/sql-prompt/
4. SQLinForm – http://www.sqlinform.com/
5. SQL Formatter – http://www.sql-format.com/
6. SQL beautifier – https://sourceforge.net/projects/fsqlf/

You may comment if you are using any other tools of your choice.

How to find index usage from cached plan in SQL Server

Today, I would like to share a script to identify the usage of index from cached plan in SQL Server. This would help us to identify the places (procs/functions etc) where the index is being used. One interesting use-case is to identify the index usage that has been introduced recently during the performance optimization.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE
(
	DatabaseName,SchemaName,TableName,IndexName,
	QueryText,QueryPlan,CacheObjectType,ObjectType
)
AS
(
SELECT	
        RelOp1.op.value(N'@Database', N'varchar(128)') AS DatabaseName,
	RelOp1.op.value(N'@Schema', N'varchar(128)') AS SchemaName,
	RelOp1.op.value(N'@Table', N'varchar(128)') AS TableName,
	RelOp1.op.value(N'@Index', N'varchar(128)') AS IndexName,
	cp.TEXT AS QueryText,cp.query_plan AS QueryPlan,
	cp.cacheobjtype AS CacheObjectType,cp.objtype AS ObjectType
FROM (SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' ) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') RelOp1 (op)
)
SELECT	QueryPlan,QueryText,CacheObjectType,	ObjectType,
		DatabaseName,	SchemaName,TableName,	IndexName
FROM CachedPlansCTE
WHERE CacheObjectType = N'Compiled Plan'
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search for a particular index*/
and (IndexName like '%Indexname%')
/* if you need to search ONLY for SELECT queries*/
and (QueryText not like '%insert%')
and (QueryText not like '%update%')
*********************************************************
*/
OPTION (MAXDOP 1)

See Also:
How to identify Missing Index from Cached Plan in SQL Server
How do you find cached plan for a procedure in SQL Server