Problem

The usage of a collection of integers in some scenarios is considered to be optimal because of the overhead involved by the creation of another user table used just to store numbers, which is the most common solution. What are collections of integers? They are lists or arrays consisting of integers.

The number of solutions used to store these collections it not small. A collection can be stored as: XML, SQLCLR used defined types, in a table variable, in a user-defined table, strings using nvarchar, ntext or varchar.

Proposed Solution

This article comes with another proposal: the collection of integers stored as MULTIPOINT spatial geometry. A MultiPoint is a collection of zero or more points. Points are objects having the following two properties mandatory: POINT(STX, STY).

Example: Instead of storing the number 5 we are going to use the spatial object POINT(0 5)

DECLARE @co GEOMETRY
SET @co = 'POINT(0 5)'
SELECT @co, @co.STAsText()

This is stored in a coordinate graph, such as the one shown below:

Conversion from number (5) to point (POINT(0 5)) could be done, by using the STY property thus:

DECLARE @i INT = 5
DECLARE @co GEOMETRY = 'POINT(0 '+LTRIM(@i)+')'  -- Instead of LTRIM function could be used the CONVERT function
SELECT @co, @co.STAsText()

Output:

Col1                                           Col2
---------------------------------------------- -----------
0x00000000010C00000000000000000000000000001440 POINT (0 5)

Note 1: spatial geometry objects are SQLCLR user-defined types and storage is binary. That’s why we have to use the STAsText() method to get the textual representation.

Note 2: The typical usage of collections is a multi-valued argument for functions and procedures.

This article describes the next operations that could be done using this new solution: collection declaration, adding new items, how to remove, cursor (looping over elements) and also collection aggregation.

Here is a collection declaration:

-- Empty collection
DECLARE @co GEOMETRY = 'MULTIPOINT EMPTY';
-- Or collection consisting from 5 points 15, 25, 40, 50, 90
DECLARE @co GEOMETRY = 'MULTIPOINT((0 15), (0 25), (0 40), (0 50), (0 90))';

We can add new elements into collection using the STUnion() method, like this:

SELECT @co = @co.STUnion('POINT(0 45)')
-- Results MULTIPOINT ((0 90), (0 50), (0 45), (0 40), (0 25), (0 15))

Note: MULTIPOINT spatial objects cannot store duplicated points

SELECT @co = @co.STUnion('POINT(0 15)').ToString() 
-- Duplicate point
-- Results: Command is executed with success (no errors) but the collection remains unmodified  
-- Results: (1 row affected)
-- Results: MULTIPOINT ((0 90), (0 50), (0 45), (0 40), (0 25), (0 15))

Removing elements by value could be done by using the STSymDifference() method like this:

SELECT @co = @co.STSymDifference('POINT(0 50)').ToString()
-- Results: MULTIPOINT ((0 90), (0 45), (0 40), (0 25), (0 15))

Maybe the biggest pro of this new solution is that items on columns could be aggregate using the UnionAggregate() method like this:

SELECT
ColID= sqelem.ColID, 
Coll= GEOMETRY::UnionAggregate(sqelem.Point).ToString()
FROM (
SELECT1, GEOMETRY::STGeomFromText('POINT(0 1)', 0)UNION ALL
SELECT1, GEOMETRY::STGeomFromText('POINT(0 2)', 0)UNION ALL
SELECT2, GEOMETRY::STGeomFromText('POINT(0 10)', 0)UNION ALL
SELECT2, GEOMETRY::STGeomFromText('POINT(0 20)', 0)UNION ALL
SELECT2, GEOMETRY::STGeomFromText('POINT(0 30)', 0)UNION ALL
SELECT2, GEOMETRY::STGeomFromText('POINT(0 40)', 0)
) sqelem(ColID, Point)

Source Code

The final source code is:

DECLARE @co GEOMETRY = 'MULTIPOINT((0 15), (0 30), (0 50), (0 90), (0 40))';
SELECT @co.ToString()
-- Add new items
SELECT @co = @co.STUnion('POINT(0 45)').ToString()
SELECT @co.ToString()
SELECT @co = @co.STUnion('POINT(0 15)').ToString() -- No duplicates
SELECT @co.ToString()
-- Delete 
-- By value
SELECT @co = @co.STSymDifference('POINT(0 50)').ToString()
SELECT @co.ToString()
-- By index (not recommended) because the order of items are automatically modified if
-- new items are added or deleted
SELECT @co = @co.STSymDifference(@co.STPointN(3)).ToString()
SELECT @co.ToString()
-- Cursor
SELECT 
ColID= ptl.number,
ColVal= @co.STPointN(ptl.number).STY
FROMmaster.dbo.spt_values ptl 
WHEREptl.type = N'P'
ANDptl.number >= 1
ANDptl.number <= @co.STNumPoints()

Final Conclusions

This article describes a new solution to manage collections of integers using SQL Server spatial data type (geometry) based on SQLCLR. Please bear in mind that this collection does not allow duplicates.