Would be great to have newid() as built-in function.
Torsten Kuhn
shared this idea
5 comments
-
Anonymous
commented
Aaaaand String to GUID cast XD
-
SD
commented
Without NEWID() or GETDATE() you can't create a unique identifier for a message. Makes it difficult to identify duplicates, this function is fundamental for failover scenarios.
-
Steve van Bodegraven commented
Here's one way -
WITH
CollectTopQuery AS
(
SELECT EventType, Sid, SessionGuid, TopOne() OVER (ORDER BY UtcTicks ASC) AS [TopOne], CollectTop(100) OVER (ORDER BY UtcTicks ASC) AS Events
FROM EventHub
GROUP BY EventType, Sid, SessionGuid, TumblingWindow(Minute, 1)
)-- Service Bus Queue
SELECT [TopOne].EventType, [TopOne].Sid, [TopOne].SessionGuid, [TopOne].UtcTicks AS WindowId
INTO ServiceBusQueue
FROM CollectTopQuery-- Table Storage
SELECT elements.ArrayIndex AS [Rank],
elements.ArrayValue.value.EventType,
elements.ArrayValue.value.UtcTicks,
elements.ArrayValue.value.Sid,
elements.ArrayValue.value.SessionGuid,
CollectTopQuery.[TopOne].UtcTicks AS WindowId,
elements.ArrayValue.value.AccelerationX,
elements.ArrayValue.value.AccelerationY,
elements.ArrayValue.value.AccelerationZ
INTO TableStorage FROM CollectTopQuery
CROSS APPLY GetElements(CollectTopQuery.Events) AS elements -
I'd like a NEWID() feature too!! The scenario is using CollectTop with Table Storage along with a message sent to a Service Bus Queue. I need the consumer (a Web Job in this case) to process the events returned from CollectTop. So, at the moment how does the consumer identify the n entities in Table Storage aggregated by CollectTop?
WITH
CollectTopQuery AS
(
SELECT EventType, CollectTop (100) OVER (ORDER BY [UTCTICKS] ASC) AS [EVENTS]
FROM EventHub
GROUP BY EventType, TumblingWindow(Minute, 1)
),
CollectTopQuery2 AS
(
SELECT EventType, Sid, SessionGuid, CollectTop (100) OVER (ORDER BY [UTCTICKS] ASC) AS [EVENTS]
FROM EventHub
GROUP BY EventType, Sid, SessionGuid, TumblingWindow(Minute, 1)
)-- Service Bus Queue
SELECT arrayElement.ArrayValue.value.EventType,
arrayElement.ArrayValue.value.Sid,
arrayElement.ArrayValue.value.SessionGuid,
NEWID()
INTO ServiceBusQueue FROM CollectTopQuery2 AS CollectTopQuery2
CROSS APPLY GetElements(CollectTopQuery2.[EVENTS]) AS arrayElement-- Blob Storage (Array)
SELECT arrayElement.ArrayIndex AS [Rank],
arrayElement.ArrayValue.value.EventType,
arrayElement.ArrayValue.value.UtcTicks,
arrayElement.ArrayValue.value.Sid,
arrayElement.ArrayValue.value.SessionGuid,
arrayElement.ArrayValue.value.AccelerationX,
arrayElement.ArrayValue.value.AccelerationY,
arrayElement.ArrayValue.value.AccelerationZ
INTO BlobStorageArray FROM CollectTopQuery AS CollectTopQuery
CROSS APPLY GetElements(CollectTopQuery.[EVENTS]) AS arrayElement-- Blob Storage (Line separated)
SELECT arrayElement.ArrayIndex AS [Rank],
arrayElement.ArrayValue.value.EventType,
arrayElement.ArrayValue.value.UtcTicks,
arrayElement.ArrayValue.value.Sid,
arrayElement.ArrayValue.value.SessionGuid,
arrayElement.ArrayValue.value.AccelerationX,
arrayElement.ArrayValue.value.AccelerationY,
arrayElement.ArrayValue.value.AccelerationZ
INTO BlobStorageLine FROM CollectTopQuery AS CollectTopQuery
CROSS APPLY GetElements(CollectTopQuery.[EVENTS]) AS arrayElement-- Table Storage
SELECT arrayElement.ArrayIndex AS [Rank],
arrayElement.ArrayValue.value.EventType,
arrayElement.ArrayValue.value.UtcTicks,
arrayElement.ArrayValue.value.Sid,
arrayElement.ArrayValue.value.SessionGuid,
arrayElement.ArrayValue.value.AccelerationX,
arrayElement.ArrayValue.value.AccelerationY,
arrayElement.ArrayValue.value.AccelerationZ
INTO TableStorage FROM CollectTopQuery AS CollectTopQuery
CROSS APPLY GetElements(CollectTopQuery.[EVENTS]) AS arrayElement -
Thank you for the suggestion Torsten!
Generating unique values is in conflict with ASA's deterministic results principle. Can you explain your scenario in some detail so that we can see how it could be tackled?
