Collections

Question

Collections

Bogdan SAHLEAN

SSC Veteran

Points: 229
More actions
November 18, 2019 at 12:00 am

#3697169

Comments posted to this topic are about the item Collections
February 26, 2025 at 3:02 am

This was removed by the editor as SPAM
February 26, 2025 at 3:03 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004561 More actions · Answer 1

I have to admit that this is a very interesting exercise but I'm not seeing anything especially practical here, especially if thousands of integers were needed in a collection.

Considering the extra complexity of the spatial code, is there a performance advantage or any other advantage that would make this more advantageous than even a table variable? And, no... I'm not looking at the complex denormalization of data onto rows using the UnionAggregate spacial functionality as an advantage for anything unless there's a pretty good performance advantage over some of the other methods.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

Hi there. Interesting concept, but some questions:

What is the practical usage of this approach? Once you have the collection of INTs in the Geometry type, how do you use the list? I don't see aggregate operations for that datatype that one can normally do with a simple list of INTs (SUM, MIN, MAX, AVG, etc). Or is this really only "a multi-valued argument for functions and procedures"? In which case, why not use a Table-Valued Parameter (TVP), which doesn't require any extra overhead for parsing?

How do you get a subset of the values? Can you order them? It looks like the only way to extract the values is via looping through and requesting each "row" individually:

DECLARE @C GEOMETRY = N'MULTIPOINT ((0 100), (0 5), (0 33), (0 -5))'

;WITH cte AS
(
  SELECT TOP(@C.STNumPoints()) ROW_NUMBER() OVER(ORDER BY @@MICROSOFTVERSION) AS [num]
  FROM   sys.objects
)
SELECT cte.[num], @C.STPointN(cte.[num]).STY
FROM   cte
-- WHERE @C.STPointN(cte.[num]).STY BETWEEN 1 AND 50
ORDER BY cte.[num];

Do you have any notes regarding the efficiency of storing values this way? As far as I can tell, it takes more space than either storing as a list of INTs or even as a delimited string:

DECLARE @SQL NVARCHAR(MAX) =
            N'DECLARE @co GEOMETRY; SET @co = N''MULTIPOINT ((0 -999)';
DECLARE @Delimited VARCHAR(MAX) = '-999';

SELECT DISTINCT TOP (1000) @SQL +=
   N', (0 '
   + CONVERT(NVARCHAR(50), so1.[object_id] + (CONVERT(BIGINT, so2.[object_id]) * 10))
   + N')',
                          @Delimited +=
   ','
   + CONVERT(VARCHAR(50), so1.[object_id] + (CONVERT(BIGINT, so2.[object_id]) * 10))
FROM master.sys.all_objects so1
CROSS JOIN master.sys.all_objects so2

SET @SQL += N')'';
SELECT @co.STNumPoints() AS [NumVals],
       DATALENGTH(@co) AS [TotalBytes],
       @co.ToString() AS [@coValue];
';

SELECT @SQL;
EXEC (@SQL);

SELECT DATALENGTH(@Delimited) AS [DelimitedBytes], @Delimited AS [DelimitedValue];
-- 1000 values == 1001, 30057 (1 second); DelimitedBytes = 11590
-- 2000 values == 2001, 60057 (8 seconds); DelimitedBytes = 22796

So, it looks like storing as a delimited VARCHAR takes up 1/3 the space (and allows for duplicates).

Why did you mention "nvarchar" as a possible alternate type to store the values as? Because sometimes people use that type, or because there is a technical reason to do so sometimes? I cannot think of a reason to use NVARCHAR as decimal digits and all common delimiters are standard ASCII and fit into VARCHAR regardless of collation.
Please don't mention NTEXT, or even TEXT, types as possibilities. Those types are way obsolete (ever since SQL Server 2005 was released) and shouldn't be used at all.
Minor point re: terminology, but regarding the statement of "spatial geometry objects are SQLCLR user-defined types", the Geometry, Geography, and HierarchyID types are system types, not user-defined. User-defined types are just that, user-defined, meaning that they are custom types based on a user-uploaded assembly.

Take care,

Solomon...

This reply was modified 5 years, 4 months ago by Solomon Rutzky.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Steve Collins SSCrazy Points: 2187 More actions · Answer 3

The article says: "The typical usage of collections is a multi-valued argument for functions and procedures." True but other solutions exist and are quite useful in comparison to a spatial representation of a list of integers. If the list of integers is unique and has a common increment then a tally tvf means you don't have to pass the actual list around at all. At least not to the db engine. Otherwise, the new string_agg() function is quite handy with integers. If you consider other types besides integers then json is quite nice.

select cast((select string_agg(n, ',') from dbo.tally(10)) as varchar(max));

select cast((select char(64+n) char_n from dbo.tally(10) for json path, root('ascii_list')) as nvarchar(max));

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Japie Botma SSCrazy Points: 2990 More actions · Answer 4

Ok, let's say you have a system with 10 roles for security access. You could put the role number in X and a 1 or 0 in Y to represent access. So then you save this field next to the user id.

Hmm, still seems to be more complex than having a table with user id, role and access.

5ilverFox
Consulting DBA / Developer
South Africa

jbowers Old Hand Points: 370 More actions · Answer 5

It's interesting that you can do this, but can you give a practical reason why you would want to do this over more traditional collections? Are there performance benefits? Storage benefits?

This reply was modified 5 years, 1 month ago by jbowers.

chudman SSCrazy Points: 2453 More actions · Answer 6

chudman

SSCrazy

Points: 2453

February 13, 2020 at 11:16 pm

#3725795

Neat, but why?

Collections

Cookies on SQLServerCentral