SHUBH KEY

Interesting SQL

Use of RowCount

SET ROWCOUNT 0

SELECT * FROM dbo.TableName ORDER BY DisplayOrder DESC

Order by Should be specific

Wrong:

SELECT codeid, CodeValue, CodeDescription

FROM dbo.TableName

ORDER BY 1, 2 DESC;

Correct:

SELECT Column1, Column2, Column3,DisplayOrder

FROM dbo.TableName

ORDER BY DisplayOrder, Column2 ;

USE OF IN/ EXISTS – Check in Execution Plan

SELECT *

FROM dbo.Table1 E

WHERE E.Column1IN ( SELECT EA.Column1

FROM dbo.Table2 EA

WHERE EA.Column1= E.Column1)

SELECT *

FROM Table1 E

WHERE EXISTS ( SELECT EA.Column1

FROM Table2 EA

WHERE EA.CustCode = E.CustCode)

Keep both side data type common as possible

SELECT job_id,order_dttm,CAST(order_dttm AS VARCHAR(10)),CONVERT(VARCHAR,order_dttm) FROM dbo.TableName WHERE order_dttm =’2021-03-23‘

———————————————————-

select  job_id as Job_Id,cust_code as Cust_Code,Company_id as Company_Id,insert_dttm  from TableName

where DateDiff(d,insert_dttm ,’2021-03-23′)=0

 

select  job_id as Job_Id,cust_code as Cust_Code,Company_id as Company_Id,insert_dttm  from TableName

where insert_dttm between ‘2021-03-23’ and ‘2021-03-24’

Avoid Loop

Do not use cursor if we really do nor require or you have alternative.

Add multiple row in single row

SELECT TOP 10 * FROM TableName

Select Issue.issue_id,

Left(Issue.issue_memos,Len(Issue.issue_memos)-1) As “Notes”

From(Select distinct ST2.issue_id,

(Select ST1.note + ‘,’ AS [text()]

From dbo.TableName ST1

Where ST1.issue_id = ST2.issue_id

ORDER BY ST1.issue_id

For XML PATH (”)) [issue_memos]

From dbo.issue_memo ST2) [Issue]

Case Sensitive Text

SELECT * FROM dbo.TableName WHERE CodeValue

COLLATE Latin1_General_CS_AS  LIKE ‘Cus%‘

ALTER TABLE TableName
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

With Ties

SELECT TOP 20 DisplayOrder FROM dbo.TableNameORDER BY DisplayOrder DESC

SELECT TOP 19 WITH TIES DisplayOrder FROM dbo.TableName ORDER BY DisplayOrder desc

Average I/O

SELECT TOP 50

(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],

substring (qt.text,qs.statement_start_offset/2,

(case when qs.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else qs.statement_end_offset end –    qs.statement_start_offset)/2)

as query_text,

qt.dbid,

qt.objectid

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt

ORDER BY [Avg IO] DESC

Top five queries by average CPU time

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END – qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

Missing Indexes for entire instance by Index Advantage

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC;

Inefficient query plans

Select

highest_cpu_queries.plan_handle,      highest_cpu_queries.total_worker_time,

q.dbid,     q.objectid,     q.number,     q.encrypted,     q.[text]

from

(select top 50

qs.plan_handle,

qs.total_worker_time

from

sys.dm_exec_query_stats qs

order by qs.total_worker_time desc) as highest_cpu_queries

cross apply sys.dm_exec_sql_text(plan_handle) as q

order by highest_cpu_queries.total_worker_time desc

Check table is seeked maximum wise

Select S.name, * from sys.dm_db_index_usage_stats D

inner join sysobjects S on D.object_id=S.id

order by user_seeks desc

Signal Waits for instance of CPU and Resource

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

AS [%signal (cpu) waits],

CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

AS [%resource waits]

FROM sys.dm_os_wait_stats OPTION (RECOMPILE);

Best Practices

Open chat