• Comments posted to this topic are about the item Collections

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

  • Hi there. Interesting concept, but some questions:

    1. 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?
    2. 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
      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];

    3. 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:
      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).

    4. 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.
    5. 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.
    6. 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,



    SQL# ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift ( company )
    Sql Quantum Leap ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • 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

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

    Consulting DBA / Developer
    South Africa

  • 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 4 years, 2 months ago by  jbowers.
  • Neat, but why?


Viewing 7 posts - 1 through 6 (of 6 total)

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