General SQL Stuff

Order of execution

When you execute a SQL Query, it will be executed in the following sequence.

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

TOP

Pivot
select * from DailyIncome  -- Colums to pivot
pivot (
   max (IncomeAmount)                                                    -- Pivot on this column
   for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]))         -- Make colum where IncomeDay is in one of these.
   as MaxIncomePerDay                                                     -- Pivot table alias
where VendorId in ('SPIKE')                               -- Select only for this vendor
Date Time format for Microsoft SQL
ANSI Warnings

http://technet.microsoft.com/en-us/library/aa259213%28v=sql.80%29.aspx

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
NO LOCK
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CONVERT
Convert DateTime to Just Date
CONVERT(Datetime,(CONVERT(CHAR(10),[DateTime],102)),102)
Convert DateTime to Date + Hour
DATE = DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0)
Datepart

MAXDOP

Limit SQL query to a single thread - for intensive SQL queries without impacting system performance for other queries

SELECT * FROM BLAH WHERE A = B
OPTION (MAXDOP 1)

http://sqlmag.com/blog/common-misconception-about-maxdop

LAST HOUR

WHERE DateTime > DateAdd(hh,-1,GetDate())
Checking Seed value
USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',NORESEED);
GO
Setting Seed value
USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',RESEED,1001);
GO
Setting constraint on a database table for two fields which must be unique
  • Enter Design View of Table
  • Right Click and select Indexs/Keys
  • Add an index, add your two (or more) colums and select unique - see screen shot below

select * from Route_Call_Detail
WHERE DateTime >= cast(GETDATE()-1 as date) AND DateTime < cast(GETDATE() as date)
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > GETDATE()-1
ORDER BY      STATS.last_execution_time DESC
SELECT * FROM
(
SELECT
TCDRecoveryKey = TCD.TCDRecoveryKey
,ECCID = TCD.ExpandedCallVariableID
,ECCValue = TCD.ECCValue
FROM [ucce_awdb].[dbo].[Termination_Call_Variable] TCD
WHERE DateTime > GetDate()-1 AND ExpandedCallVariableID IN (5039,5040,5041)
)  
AS S
PIVOT (MAX(ECCValue) FOR [ECCID] IN ([5039],[5040],[5041])
) AS PVT
SELECT * FROM
(
SELECT
 TCDRecoveryKey = TCD.TCDRecoveryKey
--,ECCID = TCD.ExpandedCallVariableID
,ECCName = ECC.EnterpriseName
,ECCValue = TCD.ECCValue
 
FROM 
[ucce_awdb].[dbo].[Termination_Call_Variable] TCD
,[ucce_awdb].[dbo].[Expanded_Call_Variable] ECC
 
WHERE 
DateTime > GetDate()-1 
AND TCD.ExpandedCallVariableID IN (5030,5036)
AND TCD.ExpandedCallVariableID = ECC.ExpandedCallVariableID
)  
AS S
PIVOT (MAX(ECCValue) FOR [ECCName] IN ([USER.cust.blah1],[USER.cust.blah2])
) AS PVT
USE [ucce_awdb]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[t_Skill_Group_Member](
	[SkillGroupSkillTargetID] [dbo].[DBINT] NOT NULL,
	[AgentSkillTargetID] [dbo].[DBINT] NOT NULL,
 CONSTRAINT [XPKSkill_Group_Members] PRIMARY KEY CLUSTERED 
(
	[SkillGroupSkillTargetID] ASC,
	[AgentSkillTargetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Subtract two dates and get the difference as a duration - reference https://stackoverflow.com/questions/14445600/datediff-in-hhmmss-format

SELECT
FORMAT(CONVERT(TIME, [appoitment].[Start] - [appointment].[End]), N'hh\:mm') AS 'Duration'
FROM
[tblAppointment] (NOLOCK)
SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[TABLES].name AS [TableName],
SUM([Partitions].[ROWS]) AS [TotalRowCount]
 
FROM sys.tables AS [TABLES]
JOIN sys.partitions AS [Partitions]
ON [TABLES].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
 
WHERE [TABLES].name NOT IN ('MSreplication_options',
'spt_fallback_db',
'spt_fallback_dev',
'spt_fallback_usg',
'spt_monitor')
 
GROUP BY SCHEMA_NAME(schema_id), [TABLES].name;

Reference: https://www.w3schools.com/sql/sql_wildcards.asp

Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
Represents any character not in the brackets h[oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt
  • Right Click and select Design Table
  • Select Table Designer
  • Select Indexes / Keys
  • Add New Index
    • Select Column
    • Set is Unique to Yes
    • Set a relevant Name
  • Save Table
 Format(tableName.DateTime,'yyyy-MM-dd') 

And probably a better way….

(CAST(tableName.DateTime AS date))
  • tech-notes/sql.txt
  • Last modified: 2021/05/14 11:39
  • by gerardorourke