JavaScript: Add and remove from arrays / arrays as collections

Did you know you can easily add and remove elements from Javascript arrays ?

Declare an array:

var arr = [];

To add an element:

arr.push(item);

To remove an element at index idx:

arr.splice(idx, 1);

To remove an element elm:

var idx = arr.indexOf(elm);

if (idx > -1) arr.splice(idx, 1);

The splice function allows adding and removing items from an array by providing a range (start index and length) and replacement values.

See also: https://www.w3schools.com/jsref/jsref_splice.asp

Sql Server Advanced: Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query.

Example:

WITH BelgianCustomers (Id, Name, Zip)

AS (
   SELECT Id, CONCAT(FirstName, ' ', LastName), ZipCode
   FROM Contact
   WHERE Type='Customer' AND CountryCode='BE'
)
SELECT Zip, COUNT(Id)
FROM BelgianCustomers
GROUP BY Zip

CTE’s can also be recursive, which for instance allows to dynamically generate ‘counting’ rows from 1 to 100. I.e:

WITH [counter]

AS (
   SELECT 1 AS [Value]
   UNION ALL
   SELECT [Value]+1 FROM [counter] WHERE [Value]<100
)
SELECT [Value] FROM [counter]

Which outputs 100 rows with 1 column [Value] with the values from 1 to 100.

Recursive CTE’s also allow to query hierarchy trees without limitation on the tree dept. I.e:

WITH [hierarchy] AS (

   -- Get 'root' personnel:
   SELECT 0 AS Level, Id, BossId, Job, Name
   FROM [Personnel]
   WHERE BossId IS NULL
   -- Union to add their subordinates recursively:
   UNION ALL
   SELECT h.Level+1 AS Level, p.Id, p.BossId, p.Job, p.Name
   FROM [Personnel] p
   INNER JOIN [hierarchy] h ON (p.BossId=h.Id)
)
SELECT * FROM [hierarchy]

(See “HierarchyThroughRecursiveCteExample.sql” hereunder to reproduce)

Some about recursive CTE’s:

https://msdn.microsoft.com/en-us/library/ms186243.aspx
https://sqlwithmanoj.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/

Note: when using common table expressions, semi-colons (“;”) are sometimes required to allow the T-SQL interpreter to correctly separate the commands.

HierarchyThroughRecursiveCteExample.sql :

-- Example of a recursive CTE (Common Table Expression) to retrieve a hierarchy:
--------------------------------------------------------------------------------
CREATE TABLE [Personnel]
(
    [Id] int IDENTITY(1,1) NOT NULL,
 [BossId] int null,
 [Job] nvarchar(50) not null,
 [Name] nvarchar(50) not null,
 CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [Personnel] ON
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (1, NULL, 'CEO', 'Eric Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (2, 1, 'CFO', 'Finn Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (3, 1, 'CIO', 'Inne Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (4, 2, 'Accountant', 'Axel Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (5, 3, 'PM', 'Patrick Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (6, 5, 'DBA', 'David Doe')
INSERT INTO [Personnel] ([Id], [BossId], [Job], [Name]) VALUES (7, 5, 'Dev', 'Dereck Doe')
SET IDENTITY_INSERT [Personnel] OFF
GO
WITH [hierarchy] AS (
   -- Get 'root' personnel:
   SELECT 0 AS Level, Id, BossId, Job, Name
   FROM [Personnel]
   WHERE BossId IS NULL
   -- Union to add their subordinates recursively:
   UNION ALL
   SELECT h.Level+1 AS Level, p.Id, p.BossId, p.Job, p.Name
   FROM [Personnel] p
   INNER JOIN [hierarchy] h ON (p.BossId=h.Id)
)
SELECT * FROM [hierarchy]
GO