Any alternative to array datatype in SQL Server

Question

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Like this:

CREATE TABLE sal_emp 
(
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

Some INSERT statements are as below

INSERT INTO sal_emp
VALUES ('Bill', '{10000, 10000, 10000, 10000}',
        '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
VALUES ('Carol', '{20000, 25000, 25000, 25000}',
        '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

SELECT * FROM sal_emp;

Output:

 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

How can we achieve the same thing in SQL Server or any alternative to it?

There is no equivalent alternative in MS SQL. Only JSON msdn.microsoft.com/en-us/library/dn921897.aspx in latest versions. — IVNSTN
– IVNSTN, Commented Nov 21, 2016 at 7:22
Muahmmad, Even if this might be supported, I would consider this as bad design. Such depending lists should live in 1:n-related side tables... This concept has a lazy smell :-) — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 21, 2016 at 7:33
@Shnugo: It's not necessarily a bad design (and it gets mis-used way too often in Postgres), if a single attribute of an entity is an array by nature then an array is a good choice. But in this case a one-to-many relationship would be a much better choice - even in Postgres — user330315
– user330315, Commented Nov 21, 2016 at 7:38
@Shnugo In Postgres this is not done via a "side table". It's single, compressable variable length data structure. Any attribute where you always read and write the entire array (for further processing in your applications) but never need to access individual elements from within SQL is a possible candidate for this (but again: the shown example is not a valid example for a good choice. I completely agree that the information shown should not be stored in array - not even in Postgres) — user330315
– user330315, Commented Nov 21, 2016 at 7:49

Muhammad Iqbal · Accepted Answer · 2016-12-27 10:28:32Z

2

There is NO alternative to array datatype in SQL Server.

answered Dec 27, 2016 at 10:28

Muhammad Iqbal

1741 gold badge1 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Any alternative to array datatype in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related