General SQL Stuff

* Index Seeks versus Index Scans

• http://www.databaseanswers.org/data_models/index.htm

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

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

CONVERT(Datetime,(CONVERT(CHAR(10),[DateTime],102)),102)

DATE = DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0)

DATEPART(yy,[myDateTime])

DATEPART(mm,[myDateTime])

CASE WHEN DATEPART(week,[myDateTime]) = 1
THEN DATEADD(day,-(DATEPART(dayofyear,[myDateTime])-1),DATEADD(day,DATEDIFF(day,0,[myDateTime]),0)) 
ELSE DATEADD(day,-(DATEPART(weekday,[myDateTime])-1),DATEADD(day,DATEDIFF(day,0,[myDateTime]),0)) END

https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

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

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

http://msdn.microsoft.com/en-us/library/ms187928.aspx

http://msdn.microsoft.com/en-us/library/ms174420.aspx

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

WHERE DateTime > DateAdd(hh,-1,GetDate())

USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',NORESEED);
GO

USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',RESEED,1001);
GO

• 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

• https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/set-up-login-accounts-database-mirroring-always-on-availability?redirectedfrom=MSDN&view=sql-server-ver15
• https://www.mssqltips.com/sqlservertip/4980/setup-and-implement-sql-server-2016-always-on-basic-availability-groups/
• https://www.mssqltips.com/sqlservertip/4980/setup-and-implement-sql-server-2016-always-on-basic-availability-groups/

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

• https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/

• https://sqlperformance.com/2013/09/t-sql-queries/random-collisions

https://alveenajoyce.wordpress.com/2020/03/31/how-to-generate-a-6-digit-random-number-in-sql-server/

• https://www.sqlshack.com/querying-remote-data-sources-in-sql-server/
• Creating Linked Database

• https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver15

• 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))

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-ver15

Here we have a column 'myColumn' with a comma separated value of (for example) 'apples,bananas' and we want FirstVariable = 'apples' and SecondVariable = 'bananas'

[FirstVariable] = SUBSTRING([myColumn],1,(CHARINDEX(',',[myColumn])-1))
[SecondVariable] = SUBSTRING([myColumn],(CHARINDEX(',',[myColumn])+1),100)

CHARINDEX find the comma and lets you know the position it is in. SUBSTRING uses start and end position to extract a substring.