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
- Avoid Cusror
- SET NOCOUNT ON statement:
- Use IF EXISTS (SELECT 1) instead of (SELECT *):
- Do not use the prefix “sp_” in the stored procedure name:
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
- Type Key word Capital