Temporal Tables in SQL Server

Definition from Microsoft: SQL Temporal Tables, also known as "system-versioned tables" are a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

What is it used for?
1). Auditing Data History- identifying who did what, when, and row-level restoration to the state of the temporal table data any point in time. (Time travel)

2). Monitor Data Deltas (ie. maintain company sales discounting policy by ensuring no product has had a price change that exceeds some company threshold (200% increase or decrease, etc.))

3). Slowly Changing Dimension management (eliminate the need for complicated ETL logic for tracking SCD- SQL Temporal Tables do this audit tracking automatically)

In Practice
Once upon a time, I worked on a project that had a "unique" (kind euphemism) legacy database schema design. This company had a custom "system" for tracking changes in data. There was no referential integrity so everything relied on application code not having any data access code bugs, which as we all know is not a particularly reliable setup.

Temporal Tables would have done the trick without all the complexity of having to manage a proprietary data versioning system that sometimes worked, oftentimes caused headaches and mass confusion over the root cause of production issues related to versioning of certain data entities.

Temporal Tables have the current or base table, as well as a history table that logs changes over time

Unless there is a specific reason that SQL Temporal Tables won't work for your scenario* you should take advantage of existing functionality like this so that you can focus on the unique functionality of your app and not waste precious time reinventing wheels.

Creating a temporal table is relatively straightforward. In SSMS (connected to an instance of SQL Server 2016 or later), select the option as seen below.

Menu option in SSMS

Here is the T-SQL for basic Temporal Table DDL:

 USE [KpItSimpl]  
 GO  
 --Create system-versioned temporal table. It must have primary key and two datetime2 columns that are part of SYSTEM_TIME period definition  
 CREATE TABLE dbo.SimplTemporalTable  
 (  
      --Table PK and columns definition  
   ProductID int NOT NULL PRIMARY KEY CLUSTERED,  
   ProductDescription varchar(max) NULL,  
      ProductPrice decimal,  
   --Period columns and PERIOD FOR SYSTEM_TIME definition  
   ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START,   
   ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END,  
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)   
 )  
  WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SimplTemporalTableHistory));   
 GO  

T-SQL DDL for creating a new Temporal Table

For a practical example, I inserted some data into this temporal table, and then committed some UPDATE statements on that data, changing the ProductPrice value of some of the product records. The query below demonstrates how we can look for records (we are likely most interested in the exact change time) that had a price change exceeding +/- 200%:

Example of a query utilizing the Temporal Table feature to see an excessive product price change

Note(!) one limitation that stands out right away- you cannot drop a temporal table like a standard SQL table. You must turn versioning off on the table, drop the table and drop the history table. So instead of selecting "Delete" from the Table menu options or executing "DROP TABLE", you will want to select "SCRIPT AS >> DROP To" option as seen below:

You must script the deletion/drop of a SQL temporal table

As you can see, SQL Temporal Tables can make managing data changes a lot easier. Certainly much easier than a proprietary change versioning system and much faster than restoring the entire database to inspect the state of data at a particular point in time.


*Temporal Table Considerations and Limitations: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=sql-server-2017

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-2017

T-SQL STUFF, STRING_SPLIT

In cases where you must extract a series of values from a delimited string, you can utilize SQL Server's built-in string_split() function. You need only specify the delimited string and the delimiting character (commas in this case ",") as the arguments.

When a requirement calls for you to roll up data into a single row, you can use STUFF() (with or without combined with FOR XML, depending on your data). Just specify an inner query as the argument for STUFF().

 --Get ids from a csv of ids  
 SELECT * FROM string_split('100,101,201,301,411,414', ',')  
 

--Push a series of values into one row  
 SELECT playerID,  
 STUFF((select '; ' + teamID  
      from Managers  
      where teamID like('%P%')  
            group by teamID  
      for xml path('')), 1, 1, '') allTeams  
 FROM Managers  

Result of these functions

T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  

XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  

XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)

3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

Dynamic SQL

"a programming technique that enables you to build SQL statements dynamically at runtime." -Oracle Docs

Dynamic SQL is a very useful tool but should only be used when absolutely necessary. Having SQL compiled at runtime amplifies the threat of SQL injection and the readability of the SQL code (dynamic SQL SPs result in large double and quadruple quoted red text which can make things much more difficult to read than when reading regular highlighted SQL code).

But when circumstances require you to use dynamic SQL to get the data you need (ie. there is no other way to structure your database environment and/or the "dynamism" can't be done equally as easy or easier using some sort of data adapter like .NET CLR code) dynamic SQL may be your best option.

For more in-depth info on this subject, see this great reference from SQL Server MVP Erland Sommarskog on this subject and the pros and cons of using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Here is a brief example of dynamic SQL:

 DECLARE @mult bit = 0  
 DECLARE @sqlToExecute varchar(500)  
 DECLARE @srvWest varchar(50) = 'WestZoneSqlSrv08'  
 DECLARE @srvEast varchar(50) = 'EastZoneSqlSrv08'  
 IF @mult = 0  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ALGO.dbo.[Order]'  
 ELSE  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ' + @srvEast + '.ALGO.dbo.[Order] UNION ALL SELECT TOP 1 OrderID, PersonId FROM ' + @srvWest + '.ALGO.dbo.Order'   
 EXEC(@sqlToExecute)  

SQL, XML, and XSLT with SSIS

A common need in any business is the exchange of data through various file formats. EDI (Electronic Data Interchange) is typically defined in a document that outlines the positions, symbols, and range of acceptable values for a given file standard (834, 872, etc.).

Example of an 834 EDI formatted file

In this exercise, we are going to very quickly turn a SQL dataset result into an XML file and apply an XSL transformation to it- all through SSIS. Using the techniques described below you will be able to create a process to transform data and write data files for any EDI standard with relative ease.

One can add .NET and other customizable code via SSIS "Execute Process" and "Script Task", however in this exercise we are going to stick to the core problem of fetching data from a SQL Server, transforming that data and then writing the result to an output/destination file.

The SSIS package design

To start, create an Integration Services project in Visual Studio 2017 (install SQL Server Data Tools if your machine does not have the Integration Services Visual Studio project template).

Our project will consist of a Data Flow Task to gather our data (SQL Server to a text file containing the results as XML), and an XML Task which will apply an XSL transformation (XSLT) and write the file to disk.

Data Flow Step #1 -  Source

Data Flow Step #1 - Destination

In my example here, we are using ADO.NET to connect to SQL Server for source data as XML and then writing that data to result.txt.

result.txt (formatted to more clearly see the Customer XML elements

Next, the SSIS XML Task takes result.txt and applies an XSL stylesheet transformation to it, thereby changing the data from XML to HTML with dynamically added CustomerID values (the integer after the static text, "***CUST**ID**").

The key to the particular (CustomerID) transformation we are doing here is the following code which selects each <Customer> in the XML and writes the <CustomerID> value after "CUST**ID**".

<xsl:for-each select="Root/Customer">
 <tr>
  <td>NM1*IL*1***CUST**ID**<xsl:value-of select="CustomerID"/>****MI*YYX123456789!</td>
 </tr>
</xsl:for-each>

XML Task

XSLT is the operation, result.txt is our source data, finally.htm our destination and the XSL is Direct Input shown in the highlighted text above.

You can easily send mail on success or failure to keep the appropriate personnel informed of ETL job/service status. You can use a connected SMTP server to send mail via the SSIS Send Mail Task or (as I have in this example) you can use this T-SQL for sending mail as an Execute SQL Task:

T-SQL for sending email in Execute SQL Task

If you need to create a SQL Server Database Mail account/profile for Gmail, etc, follow this walkthrough.

And with a little luck and no missed steps, you will execute the SSIS package successfully and can go on to really leverage the powerful capabilities of SSIS to build simple and effective solutions for data interchange.

The completed SSIS package and the .htm result of our ETL exercise

Check SQL Server Version from T-SQL

Find all instances of a string within a string in T-SQL

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) 
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Simply use the function above.

If you want to understand technically how this works, see the reference below.

Essentially, this function dynamically creates a table (CTE) of the term being searched for with Number being the Row_Number of each charindex within the string.

This CTE is then searched to elicit the starting positions of each location where string term matches ([WHERE] SUBSTRING(@term + @string, Number, LEN(@term)) = @term).

Usage:

The part: "SELECT ROW_NUMBER() OVER (ORDER BY [object_id].." is there to provide space to establish the CTE with which to store search string indexes. If you need more space to traverse over (your search string is > 2k characters) you can use sys.all_columns instead of sys.all_objects or a CROSS JOIN for tables with greater than 8k records.


Reference: https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

All credits: Aaron Bertrand. He is a T-SQL genius. If it can be done in T-SQL, Aaron has done it, benchmarked it and probably blogged about it: https://sqlperformance.com/author/abertrand

Useful T-SQL Dates Reference

For short US datetime format use this:

select convert(varchar, getDate(), 1) --10/15/2017

For a list of most others (SQL Server), see: https://rauofthameem.wordpress.com/2012/09/14/sql-date-time-conversion-cheat-sheet/

On to the useful SQL dates:

select dateadd(mm, datediff(mm, 1, getDate()), 0) as FirstaTheMonth

select dateadd(ms, -3, dateadd(mm, datediff(m, 0, getDate()) + 1, 0)) as LastaTheMonth

select dateadd(qq, datediff(qq, 0, getDate()), 0) as FirstDayOfQtr

select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, getDate()), getDate())), 0) --(First Monday of Month)

Great T-SQL References:

http://www.gehbeez.com/sql-get-first-and-last-day-of-a-week-month-quarter-year/

http://brianvanderplaats.com/cheat-sheets/T-SQL-Cheat-Sheet.html

Invisible characters from pasted data

If you find yourself debugging an ETL task error where the problem at hand is data type mismatch or some other data error that is not visible to you in the source (culprit) data, don't fret.

What has likely happened is invisible formatting characters got into the data.

To remedy things, all you'll need to do is open up Notepad++, paste your data into the text window, and from the View menu dropdown, select "Show Symbol" >> "Show All Characters".

Simply edit the data in Windows Notepad (remove the special characters), save, and try to import again. Your data should be sufficiently scrubbed clean and your ETL task will return to normal.

There is also a shortcut button in Notepad++:

PS: handy tip for Windows users- to remove all formatting from copied data, paste the text into Notepad. All formatting will be removed from the data. Re-select it and go paste where needed.

PureText is another application that handles removing all formatting from text: https://stevemiller.net/puretext/

Developer Scratchpad (.NET C#, VB, JavaScript, Powershell, Python, SQL, etc.)

If you memorize infrequently used code, you are wasting mental storage space- reference notes!

--------NETWORK-----------------------------------------------------------
--------------------------------------WINDOWS BUILTIN CMDs-------------------------------------
//converting Windows back slashes to Unix forward slashes
SET string=C:\Users\Admin\Desktop\DeskDEV\
ECHO %string:\=/%
//converting Unix forward slashes to Windows back slashes
SET string=C:/Users/Admin/Desktop/DeskDEV/
ECHO %string:/=\%
netsh interface ip show config
netsh interface ip set address "Local Area Connection" static 192.168.0.10 255.255.255.0 192.168.0.254
netsh interface ip set address "Local Area Connection" dhcp
netsh http show urlacl
netsh int tcp set heuristics disabled
netsh int tcp set global autotuninglevel=disabled
netsh int tcp set global rss=enabled
netsh int tcp show global
netsh winsock reset
netsh int ip reset
ipconfig /release
ipconfig /renew
ipconfig /flushdns
ipconfig /registerdns
msdt.exe -id NetworkDiagnosticsWeb
msdt.exe -id NetworkDiagnosticsInbound
msdt.exe -id NetworkDiagnosticsNetworkAdapter
ipconfig
sfc /scannow
driverquery
ping and pathping
tasklist and taskkill
nslookup
sigverif
doskey /history
netstat
cipher
getmac
net user
arp
nbstat
logman
route
shutdown
assoc
schtasks
attrib
auditpol
bcdboot
bcdedit
bootcofg
bootsect
cacls
call
cd
certreq
certutil
chcp - (active code page)
checknetisolation
chkdsk
chkntfs
choice - ([Y,N]? prompt in .bat)
cipher - (enc status of files)
clip
cls
cmstp - (connectionMgr interesting*)
comp - (basic file diff)
compact - (compress)
convert - (file system type)
copy
cscript - (execute vbs, etc.)
date
debug - (only on DOS && 32bit Windows)
defrag
del
deltree
dir
diskpart*
diskperf
diskraid
dism - (dply .isos)
dispdiag
djoin - (join domain)
driverquery - (all driver information!)
echo
endlocal - (break out of a script's localalized context)
erase
esentutl - (storage engine mgmt)
eventcreate
expand - (unzip archive)
Extrac32 - (unzip .cab)
extract - (unzip archive)
fc - (compare file sets)
find - (srch for text in file or files)
Findstr - (regex "")
finger - user(s) info
Fltmc - (filter driver installer)
fondue - (new/experimental Windows features)
format - (format drive for some file system)
fsutil - (filesys utiles)
ftp - (chell for ftp comms; "quit")
ftype - default programs
getmac - get MAC of all network controllers on system
gpresult - (grp policy)
gpupdate
icacls
iscsicli - (iSCSI initiator)
klist - Kerberos tickets
Ksetup - (for a kerberos-enabled server)
ktmutil - (kernal transaction mgr utility)
label - (volume label of disks)
licensingdiag - (Windows product activation info)
loadhigh(lh), lock - (old DOS cmds)
lodctr - (registry values for perf counters)
logman - (event trace session)
logoff - (until Win7)
makecab - (cainet maker)
manage-bde - (Bitlocker drive enc)
mklink - (create symbolic link)
mode - (COM/LPT ports - check "CON" device further)
mofcomp - .mof* file display
more
mount - (until SFU was discontinued...)
mountvol - (use this instead)
move
mrinfo - (rounter info)
msg
msiexec - (start an install)
muiunattend - (unattended install)
nbtstat - (see connection info)
net - (cfg network settings)
net1 - ("" "" Y2K issue wkrnd/patch for net)
netcfg - (installs WinPE)
netsh - (network shell, net mgmt)
netstat - (displays listening ports, connects)
nltest - (test secure channels between machines across trusted domains)
nslookup - (hostname from IP)
openfiles - (remote cmd)
path
pathping - (tracert w/net latency, more info)
pause - (Press any key to continue...)
ping
pkgmgr - (windows pkg mgr)
pnpunattend - (unattended driver install)
pnputil
popd
powercfg
print
prompt
pushd
rasautou
rasdial*
rcp - (deprecated)
rd
reagentc - (win recovery)
recover - (recover a file deleted, but not yet overwritten)
reg - (registry cmds)
regini - (hange registry permissions and registry values from the command line)
Regsvr32 - (register .dll as component)
relog - (new perf append to existuing perf data)
rem
ren
repair-bde - (Bitlocker repair)
replace
rmdir
robocopy - (robust copy)
route - (maniupulate network routing tables)
Rpcping
runas
schtasks
sdbinst - (SDB db files, used by OpenOffice)
secedit - (compare current sec levels to a template to ensure no unneeded access, etc.)
setspn - (set service principle name for an AD account)
setx - (user env variable config)
sfc - (System File Checker)
shutdown - (automate poweroff)
sort
sxstrace - tracing
subst - (assoc local path with drive letter; similiar to net use)
takeown - regain access to object
taskkill - (force kill a process)
tasklist - (running proccesses; used by taskmgr.exe, etc.)
tcmsetup - (to set up or disable the Telephony Application Programming Interface (TAPI) client)
telnet
tftp (deprecated?)
time
timeout
title - (cmd prompt window label)
tracerpt - trace rpt
tracert - Internet Control Message Protocol (ICMP) Echo Request messages to a specified remote computer with increasing Time to Live (TTL) field values and displays the IP address and hostname, if available, of the router interfaces between the source and destination.
tree - (awesome)
typeperf - (cmd perf data)
tzutil - (time zones)
unlodctr - (uninstalls a perf counter in registry)
vaultcmd - (manage Windows stored creds)
ver
vol
vssadmin - (volume shadow copy admin)
w32tm - (windows time)
wecutil - (win evt collector)
where - (find by regex)
whoami
winmgmt - (WMI)
winrm - (remote mgmt)
winrs - (remote sh)
winsat - (system assesment tool)
wmic - (WMI CLI)
wsmanhttpconfig
xcopy
xwizard - (register data in Windows, often from a preconfigured XML file)


-------POWERSHELL---------------------------------------------------
Get-Command -Name {..}
get-alias
Get-Help Get-EventLog
Get-ChildItem c:/
$var
(no concept of scope- ALL is glbl)
Remove-Item
//ex. of getting some scv column data...
Get-Command -Verb Export | Select-Object CommandType, Name, Version, Source | Export-Csv -NoTypeInformation -Path C:\NewFolder\ExportCommands.CSV
//Outfile is useful
Get-Command -Verb Export | Select-Object CommandType, Name, Version, Source | Out-File C:\NewFolder\ExportCommands.txt
Get-Process -Id 10500 | Stop-Process
Get-EventLog -LogName System -Newest 5
Clear-EventLog "Windows PowerShell"
Get-WmiObject -List -Class Win32*
Enter-PSSession Computer1
Invoke-Command -ComputerName Computer1, Computer2, Computer3, -ScriptBlock {Get-Process}
//Create a new (persistant) session, and save it
$session = New-PSSession -ComputerName Computer1, Computer2
Set-ExecutionPolicy
Get-ExecutionPolicy
Get-Service | Where-Object {$_.status -eq "stopped"}
//Create html from basic data structs (in this case, ps cmdlet aliases)
get-alias | convertto-html > aliases.htm
invoke-item aliases.htm
Get-EventLog -Log "Application"
Stop-Process -processname note*
Get-Service | Where-Object {$_.Status -eq 'Running'}
Get-AuthenticodeSignature somescript.ps1 @(Get-ChildItem cert:\CurrentUser\My -codesigning)[0] -IncludeChain "All" -TimestampServer "http://timestamp.verisign.com/scripts/timstamp.dll"
Get-Process | ForEach-Object {Write-Host $_.name -foregroundcolor cyan}
Clear-Content C:\Temp\TestFile.txt
Clear-Content -path * -filter *.TXT –force (all text files force clear)
Checkpoint-Computer -Description "My 2nd checkpoint" -RestorePointType "Modify_Settings"
Get-ComputerRestorePoint | format-list
Compare-Object $obj1 $obj2
 [string[]]$arrayFromFile = Get-Content -Path 'C:\someplace\file.txt'
 foreach ($uri in $arrayFromFile) {
   yt-dl -f 140 $uri
  }
$settings = $TextData | ConvertFrom-StringData
ConvertTo-SecureString [-String] SomeString
Get-Service wuauserv -ComputerName chi-dc04,chi-p50,chi-core01 |
Export-Clixml -Path c:\work\wu.xml
Get-AppLockerPolicy
New-ItemProperty
$ourObject = New-Object -TypeName psobject
/////////////////////////////////////////////////////
$url = http://<webapp>.azurewebsites.net/CreateSite.asmx
$proxy = New-WebServiceProxy $url
$spAccount = "<username>"
$spPassword = Read-Host -Prompt "Enter password" –AsSecureString
$projectGuid = ""
$createOneNote = $false
$proxy | gm -memberType Method
New-WSManInstance winrm/config/Listener
-SelectorSet @{Address="*";Transport="HTTPS"}
-ValueSet @{Hostname="Test01";CertificateThumbprint="01F7EB07A4531750D920CE6A588BF5"}
/////////////////////////////////////////////////////
Get-Process | Sort-Object name -Descending | Select-Object -Index 0,1,2,3,4
New-Alias np c:\windows\system32\notepad.exe
Set-StrictMode
Wait-Job
Write-Progress -PercentComplete (($var1/$var2) * 100))
$job = Start-Job -ScriptBlock { Set-PSBreakpoint C:\DebugDemos\MyJobDemo1.ps1 -Line 8; C:\DebugDemos\MyJobDemo1.ps1 }
Debug-Job $job
Get-Counter -Counter "\Processor(_Total)\% Processor Time" -SampleInterval 2 -MaxSamples 3
Test-Path C:\Scripts\Archive
Get-WinEvent -ListLog *
Invoke-TroubleshootingPack (Get-TroubleshootingPack C:\Windows\diagnostics\system\networking)
Measure-Command
Get-Process | Measure-Object VirtualMemorySize -Sum
New-EventLog -LogName Wtf_Log -Source SomewhereOutThere
Receive-Job -Name HighMemProcess
Register-EngineEvent PowerShell.Exiting -Action {"PowerShell exited at " + (Get-Date) | Out-File c:\log.txt -Append}
Register-ObjectEvent
Remove-Event
Set-PSDebug
Start-Sleep
Tee-Object
Test-AppLockerPolicy
Test-ComputerSecureChannel
Trace-Command
Write-Debug "`$return has $($return.count) items"

-------VISUAL STUDIO----------------------
Ctrl+, . for Autosuggest
Sort usings by Right-click on using references

--------.NET------------------------------------------------------
--------------------------C#--------------------------------------
//switch expression*
car foo = line switch {
case 0: foo = 1; break;
case 0: foo = 1; break;
default: for = 0; break;
}
XDocument.Parse is for string XML
XDocument.Load is for XML *Files*
[,] is multidimensional array and [][] is array of arrays.
//collection initializer
List<Coord> ls = new List<Coord>() { new Coord { x = 1, y = 2 }, new Coord { x = 11, y = 12 }};
//lambda expression value
var filteredList = list.Where(o => o.id == targetGUID);
//Make the internals visible to the test assembly for unit testing private dependencies, etc.
[assembly: InternalsVisibleTo("MyTestAssembly")] in the AssemblyInfo.cs file.
//Obsolete attribute to warn that an assembly may go away and that alternatives should be used asap
Obsolete("This method will be deprecated soon. You could use XYZ alternatively.")]
//To prevent a repetition of the execution, change the LINQ result to List after execution
var result = masterCollection.Where(i => i > 100).ToList();
Console.WriteLine(result.Count()); //no extra Linq executions
Console.WriteLine(result.Average()); //no extra Linq executions
//catch Generic Exception and throw for more detail
catch (Exception)
      throw;
//Generic Type using a common Interface
protected class MyGenricClass<T> where T : IMyInterface
public void MyGenericMethod<T>(T t) where T : IMyInterface
//using obj reference for brevity and cleaner code
using (SqlConnection conn = new SqlConnection(strConnection)) { ... }
//aslias namespaces for brevity if you need to explicitly spell out the namespace anywhere
using Excel = Microsoft.Office.Interop.Excel;
var excelapp = new Excel.Application();
//object initializer
Janitor jenkins = new Janitor() { Name = "Jenkins", Address = "1212 DeplPipelnSciptzzz" };
//null checks
if (string.IsNullOrEmpty(s))
//Connection String
"Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;"
//Entity Framework Optimizer/Escape Hatch
using (var db = new MyContext())
{
    db.Query<FooBar>().FromSql("SELECT ProductID, udf_GetNameFromId(ID3) FROM Products");
}
//JSON Serialize and Deserialize with Newtonsoft
string serial = JsonConvert.SerializeObject(genericObject);
var deserial = JsonConvert.DeserializeObject<APIGenericItemsResponse>(await response.Content.ReadAsStringAsync());
//XML Serialize using System.Xml.Serialization
 public void TestSerialize(XMLEntityCollection ents)
     {
       XmlSerializer xmlSerializer = new XmlSerializer(ents.GetType());
       using (StringWriter stringWriter = new StringWriter())
       {
         xmlSerializer.Serialize(stringWriter, ents);
         Assert.IsNotNull(stringWriter);
       }
     }
//XML Deserialize using System.Xml.Serialization
XmlSerializer serializer = new XmlSerializer(typeof(XMLEntityCollection));
       using (StringReader reader = new StringReader(xml))
       {
         XMLEntityCollection ents = (XMLEntityCollection)serializer.Deserialize(reader);
         TestSerialize(ents);
       }
-------------------------VB--------------------------------------
Dim i As Integer = 1
Begin Function(ByVal a As String) As Double
     Return 37
End Function
Namespace MyNamespace
End Namespace
Public Class Manager
     Inherits Employee
End Class
Public Overridable Function CalculateSalary() As Integer
End Function
Public Overrides Function CalculateSalary() As Integer
End Function
MyBase (base in C#)
Public Interface IEmployee
    Property EmployeeID() As Integer
    Property Name() As String
    Property Age() As Integer
    Function CalculateSalary() As Integer
End Interface
Public Class Manager
Implements IEmployee
End Class

--------PYTHON-----------------------------
#cool-youtube-API
youtube-dl -f 140 $uri
#formatting opts for youtube-dl

[info] Available formats for P9pzm5b6FFY:
format code extension resolution  note 
140         m4a       audio only  DASH audio , audio@128k (worst)
160         mp4       144p        DASH video , video only
133         mp4       240p        DASH video , video only
134         mp4       360p        DASH video , video only
135         mp4       480p        DASH video , video only
136         mp4       720p        DASH video , video only
17          3gp       176x144     
36          3gp       320x240     
5           flv       400x240     
43          webm      640x360     
18          mp4       640x360     
22          mp4       1280x720    (best)

#reverse string
a =  "NotPalindrome"
print("Reverse is",a[::-1])
#transpose matrix
mat = [[1, 2, 3], [4, 5, 6]]
zip(*mat)
#store array as indiv vars
a = [1, 2, 3]
x, y, z = a
#print array elments as string
a = ["This", "is", "a", "string"]
print(" ".join(a))
#print coords of 2 combined lists
list1 = ['a', 'b', 'c', 'd']
list2 = ['p', 'q', 'r', 's']
for x, y in zip(list1,list2):
...    print(x, y)
#switch var vals in 1 line
a=2
b=4
b, a =a, b
#execute recursion without loops
print("cool"*2+' '+"beans"*5)
#convert matrix to list without loop
import itertools
a = [[1, 2], [3, 4], [5, 6]]
list(itertools.chain.from_iterable(a))

-------JAVASCRIPT------------------------------------
//Use "var" or "let" when creating any variables Assignment to an undeclared variable automatically //results in a global variable being created. Avoid global variables.
var a = 'not Globl';
=== instead of == (former compares value and type)
//Be careful when using typeof, instanceof and constructor.
var arr = ["a", "b", "c"];
typeof arr;   // return "object"
arr  instanceof Array // true
//get random item from an array
var items = [12, 548 , 'a' , 2 , 5478 , 'foo' , 8852, , 'Doe' , 2145 , 119];
var  randomItem = items[Math.floor(Math.random() * items.length)];
//get random number in a specific range
var x = Math.floor(Math.random() * (max - min + 1)) + min;
//Trim String prototype extension function
String.prototype.trim = function(){return this.replace(/^s+|s+$/g, "");};
//Append array to array
var array1 = [12 , "foo" , {name "Joe"} , -2458];
var array2 = ["Doe" , 555 , 100];
Array.prototype.push.apply(array1, array2);
//Object to Array
var argArray = Array.prototype.slice.call(arguments);
//Verify numeric
function isNumber(n){
    return !isNaN(parseFloat(n)) && isFinite(n);
}
//verify Array type
function isArray(obj){
    return Object.prototype.toString.call(obj) === '[object Array]' ;
}
//Max and Min
var  numbers = [5, 458 , 120 , -215 , 228 , 400 , 122205, -85411];
var maxInNumbers = Math.max.apply(Math, numbers);
var minInNumbers = Math.min.apply(Math, numbers);
//Empty the array
var myArray = [12 , 222 , 1000 ];
myArray.length = 0;
//use splice() not delete() for removing JS array elements
var items = [12, 548 ,'a' , 2 , 5478 , 'foo' , 8852, , 'Doe' ,2154 , 119 ];
items.length; // return 11
items.splice(3,1) ;
items.length; // return 10
//use map() to loop through array
var squares = [1,2,3,4].map(function (val) {
    return val * val;
});
//rounding a decimal numeric
var num =2.443242342;
num = num.toFixed(4);
//check object props
for (var name in object) {
    if (object.hasOwnProperty(name)) {
        // do something with name                 
    }
}
//Serialize and Deserialize
var person = {name :'Saad', age : 26, department : {ID : 15, name : "R&D"} };
var stringFromPerson = JSON.stringify(person);
/* stringFromPerson is equal to "{"name":"Saad","age":26,"department":{"ID":15,"name":"R&D"}}"   */
var personFromString = JSON.parse(stringFromPerson);
/* personFromString is equal to person object  */
//Best/fastest JS looping structure
var sum = 0;
for (var i = 0, len = arrayNumbers.length; i < len; i++) {
    sum += arrayNumbers[i];
}
//Pass functions (not string ref of function) to SetTimeout() and SetInterval()
setInterval(doSomethingEverySecond, 1000);
setTimeout(doSomethingEveryFiveSeconds, 5000);
//Clone a type
function clone(object) {
    function OneShotConstructor(){};
    OneShotConstructor.prototype= object;
    return new OneShotConstructor();
}
clone(Array).prototype ;  // []
//HTML escape function
function escapeHTML(text) {
    var replacements= {"<": "&lt;", ">": "&gt;","&": "&amp;", """: "&quot;"};                   
    return text.replace(/[<>&"]/g, function(character) {
        return replacements[character];
    });
}
//Set timeouts on async function calls
var xhr = new XMLHttpRequest ();
xhr.onreadystatechange = function () {
    if (this.readyState == 4) {
        clearTimeout(timeout);
        // do something with response data
    }
}
var timeout = setTimeout( function () {
    xhr.abort(); // call error callback
}, 60*1000 /* timeout after a minute */ );
xhr.open('GET', url, true);

xhr.send();

//deal with websocket timeout
var timerID = 0;
function keepAlive() {
    var timeout = 15000;
    if (webSocket.readyState == webSocket.OPEN) {
        webSocket.send('');
    }
    timerId = setTimeout(keepAlive, timeout);
}
function cancelKeepAlive() {
    if (timerId) {
        cancelTimeout(timerId);
    }
}

-------T-SQL-----------------------------------------------------------
SELECT dateadd(mm, datediff(mm, 1, getDate()), 0) as FirstOfTheMonth
SELECT dateadd(ms, -3, dateadd(mm, datediff(m, 0, getDate()) + 1, 0)) as LastOfTheMonth
SELECT dateadd(qq, datediff(qq, 0, getDate()), 0) as FirstDayOfQtr
SELECT dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, getDate()), getDate())), 0)  --(First Monday of Month)
--Find all instances of a 'string' within a string (varchar/nvarchar)
CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT pos = Number - LEN(@term)
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number,
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);
--Windowing for running totals, referencing related rows and row group totals
SELECT xact_amt,
     SUM(xact_amt) OVER (ORDER BY xact_datetime) AS running_total
FROM SOME_DB.dbo.SOME_TABLE
 --Get ids from a csv of ids
 SELECT * FROM string_split('100,101,201,301,411,414', ',')
--Push a series of values into one row
 SELECT playerID,
 STUFF((select '; ' + teamID
      from Managers
      where teamID like('%P%')
            group by teamID
      for xml path('')), 1, 1, '') allTeams
 FROM Managers
--Dynamic SQL
 DECLARE @mult bit = 0
 DECLARE @sqlToExecute varchar(500)
 DECLARE @srvWest varchar(50) = 'WestZoneSqlSrv08'
 DECLARE @srvEast varchar(50) = 'EastZoneSqlSrv08'
 IF @mult = 0
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ALGO.dbo.[Order]'
 ELSE
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ' + @srvEast + '.ALGO.dbo.[Order] UNION ALL SELECT TOP 1 OrderID, PersonId FROM ' + @srvWest + '.ALGO.dbo.Order' 
 EXEC(@sqlToExecute)
--CTE
WITH Entity (EntityID, Name, ParentID, Tree)
AS
(
   SELECT ID, Val1, Val2 FROM SomeDbServer.dbo.SomeDatabase;
)

SELECT * FROM Entity;

-------GIT------------------------------------------------------
git log
git diff
git init
git branch
git branch -a
git branch <branchname> ((new branch))
git switch <branchname> ((same as checkout))
git merge <branchname> ((merge another branch into current working branch))
git push origin <branchname>
git push -u origin <branchname>
git pull origin <branchname>
git remote add origin <ssh or https URI>

--------WinDbg--------
!sos.threadpool (Strike subset of modules useful (yet safe) for debugging .NET)
!sos.savemodule
!sos.finalizequeue
!mex.us
!mex.aspxpagesext
!mex.mthreads / !sos.threads
!mex.dae
!mex.clrstack2

!sym noisy
g - (Go)
r - (Registers)
d - (virtual memory commands)
!teb - (thread environment block)
!peb - (process execution block)
~{thread id}|| ~*k to execute "each thread" and assoc k cmd on them
k - (look at stacks in debugger => top is end, bottom is init/start 00000000)
? - (evaluation cmd to get HEX equiv)
lm (loaded modules (lm v m malware => show verbose module (m)atching 'malware'))
du - (display Unicode value)
.crash - (forces blue screen crash and .dmp)

-------REGEX--------------------------------------------------
(http|ftp|https)://([\w_-]+(?:(?:\.[\w_-]+)+))([\w.,@?^=%&:/~+#-]*[\w@?^=%&/~+#-])?
File Path with Filename and extension
/((\/|\\|\/\/|https?:\\\\|https?:\/\/)[a-z0-9 _@\-^!#$%&+={}.\/\\\[\]]+)+\.[a-z]+$/
 File Path with optional Filename, extension
/^(.+)/([^/]+)$/
File Name with extension having 3 chars
/^[\w,\s-]+\.[A-Za-z]{3}$/
/* Match IPv4 address */
/^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$/
/* Match IPv6 address */
/(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))/
/* Match both IPv4, IPv6 addresses */
/((^\s*((([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5]))\s*$)|(^\s*((([0-9A-Fa-f]{1,4}:){7}([0-9A-Fa-f]{1,4}|:))|(([0-9A-Fa-f]{1,4}:){6}(:[0-9A-Fa-f]{1,4}|((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){5}(((:[0-9A-Fa-f]{1,4}){1,2})|:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){4}(((:[0-9A-Fa-f]{1,4}){1,3})|((:[0-9A-Fa-f]{1,4})?:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){3}(((:[0-9A-Fa-f]{1,4}){1,4})|((:[0-9A-Fa-f]{1,4}){0,2}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){2}(((:[0-9A-Fa-f]{1,4}){1,5})|((:[0-9A-Fa-f]{1,4}){0,3}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){1}(((:[0-9A-Fa-f]{1,4}){1,6})|((:[0-9A-Fa-f]{1,4}){0,4}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(:(((:[0-9A-Fa-f]{1,4}){1,7})|((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$))/
Elements with Attributes /<\/?[\w\s]*>|<.+[\W]>/
Slug /^[a-z0-9]+(?:-[a-z0-9]+)*$/
Digits
    Whole Numbers – /^\d+$/
    Decimal Numbers – /^\d*\.\d+$/
    Whole + Decimal Numbers – /^\d*(\.\d+)?$/
    Negative, Positive Whole + Decimal Numbers – /^-?\d*(\.\d+)?$/
    Whole + Decimal + Fractions – /[-]?[0-9]+[,.]?[0-9]*([\/][0-9]+[,.]?[0-9]*)*/
Alphanumeric without space – /^[a-zA-Z0-9]*$/
Alphanumeric with space – /^[a-zA-Z0-9 ]*$/
Common email Ids – /^([a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,6})*$/
Uncommon email ids – /^([a-z0-9_\.\+-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/
Password Strength
Complex: Should have 1 lowercase letter, 1 uppercase letter, 1 number, 1 special character and be at least 8 characters long
/(?=(.*[0-9]))(?=.*[\!@#$%^&*()\\[\]{}\-_+=~`|:;"'<>,./?])(?=.*[a-z])(?=(.*[A-Z]))(?=(.*)).{8,}/
Moderate: Should have 1 lc letter, 1 UC letter, 1 number, and be at least 8 characters long
/(?=(.*[0-9]))((?=.*[A-Za-z0-9])(?=.*[A-Z])(?=.*[a-z]))^.{8,}$/
/* Username */
    Alphanumeric string that may include _ and – having a length of 3 to 16 characters –
    /^[a-z0-9_-]{3,16}$/
/* Date Format YYYY-MM-dd */
/([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))/
    Time Format HH:MM 12-hour, optional leading 0
    /^(0?[1-9]|1[0-2]):[0-5][0-9]$/
    Time Format HH:MM 12-hour, optional leading 0, Meridiems (AM/PM)
    /((1[0-2]|0?[1-9]):([0-5][0-9]) ?([AaPp][Mm]))/
    Time Format HH:MM 24-hour with leading 0
    /^(0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$/
    Time Format HH:MM 24-hour, optional leading 0
    /^([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$/
    Time Format HH:MM:SS 24-hour
    /(?:[01]\d|2[0123]):(?:[012345]\d):(?:[012345]\d)/


/* PowerShell has interesting return value behavior; vars are kept in global scope unless explicitly removed
$a = "Hello, World"
return $a
$a = "Hello, World"
$a
return */

-------DATA LAKE--------
Intermittent data holding area; a function of time restrictions, best for unstruct'd data/Ops Log/Config Data....... (for monitoring apps before a problem happens or gets worse)



References:

https://modernweb.com/45-useful-javascript-tips-tricks-and-best-practices/

https://pythontips.com/2015/04/19/nifty-python-tricks/#more-580

https://www.freelancer.com/community/articles/top-16-c-programming-tips-tricks

https://docs.microsoft.com/en-us/dotnet/csharp/tutorials/inheritance 

https://www.debuggex.com/

https://digitalfortress.tech/tricks/top-15-commonly-used-regex/