November 18, 2019 at 12:00 am
Comments posted to this topic are about the item Collections
November 18, 2019 at 5:31 am
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
Change is inevitable... Change for the better is not.
November 18, 2019 at 4:10 pm
Hi there. Interesting concept, but some questions:
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];
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).
Take care,
Solomon...
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
November 18, 2019 at 6:10 pm
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
February 13, 2020 at 6:55 am
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
February 13, 2020 at 11:16 pm
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