Tag: T-SQL

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

How to identify Missing Index from Cached Plan in SQL Server

Here, we are going to find a simple and very efficient way to identify missing indexes in SQL Server. The method is used to query cached plan in SQL Server and identify the missing index information from the Cached plan.

Please have a look and share your thoughts on the same.

Script


;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
	query_plan, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
	n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
	DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
	OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
	n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
	(   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY' FOR  XML PATH('')) AS equality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY' FOR  XML PATH('')) AS inequality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE' FOR  XML PATH('')) AS include_columns,
		tab.text,ObjectName
FROM
(
   SELECT query_plan,text,ObjectName   FROM (
			SELECT usecounts,cacheobjtype,objtype,query.text,object_name(query.objectid) ObjectName ,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' ) qs
   WHERE qs.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan,text,ObjectName)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1

First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.