1

In PostgresSQL I used array types for storing ids, uuids etc like:

CREATE TABLE IF NOT EXISTS eventing.notifications (event_type integer NOT NULL, transport_type integer, user_id uuid, user_ids uuid[]);

or

CREATE TABLE IF NOT EXISTS public.sources (name character varying(255), timestamp without time zone, read_only_organization_ids integer[], organization_id uuid);

Is there an equivalent using Microsoft SQL server in Azure?

Thom A
  • 88,727
  • 11
  • 45
  • 75
user122222
  • 2,179
  • 4
  • 35
  • 78
  • 3
    SQL Server has no array types. – Panagiotis Kanavos Oct 09 '20 at 09:56
  • 3
    Even if SQL server *did* support array types, I would suggest a **proper** normalised approach anyway. – Thom A Oct 09 '20 at 09:57
  • 2
    That's a bad design for PostgresSQL and relational databases in general. The contents of the array can't be indexed which means any queries or joins that need to use those IDs would have to scan the entire table for matches. Relations are represented by *tables*. That's why they're called relational - because they store the relations. A row represents one relation between the values, *not* one complex value. Indexes help accelerate queries based on the store values, not parts of the values – Panagiotis Kanavos Oct 09 '20 at 09:59
  • The SO question [Can PostgreSQL index array columns?](https://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns) shows that while individual item indexing is possible, it's complicated and *module-specific*, not just product-specific . Do you really need that complexity just to avoid a 1:M table? Never mind adding constraints or extra information to the relation – Panagiotis Kanavos Oct 09 '20 at 10:07

1 Answers1

1

This is blurring the lines between SQL Server and nosql, however you can do it by encoding your array into a json array and store that in a varchar(max) column.

Then to create the json array from some other table storing user ids you would use the for json clause.

To get the original array out of the varchar column you can cross apply with the openjson function:

declare @notifications table (user_ids varchar(max))

declare @user_ids varchar(max)

;with cte_jsonUser(jsonIds) as
(
    select id
    from (values(1), (2)) as tbluser(id)
    for json auto
)
insert into @notifications(user_ids)
select replace(replace(jsonIds,'{"id":',''),'}','')
from cte_jsonUser

select user_ids from @notifications

-- user_ids
-- [1,2]

select i.user_ids
from @notifications as n
cross apply openjson(n.user_ids)
with (user_ids int '$') as i

-- user_ids
-- 1
-- 2
SQL Sifu
  • 71
  • 4