I use ms sql and i need to create a table with a array column of nvarchar. What is the correct query ?
13
-
5Not possible. SQL Server does not support arrays - but that's a bad idea to begin with. Why don't you properly normalize your data model?user330315– user3303152019-07-08 09:08:07 +00:00Commented Jul 8, 2019 at 9:08
-
Ttere are no array columns. In SQL, the language, fields should contain single values. It could be a single complex value like a coordinate but it's still a single value that's treated as one item. Even in databases that have arrays, like Oracle, they are treated as a single item and their contents can't be queried individually. What are you trying to do?Panagiotis Kanavos– Panagiotis Kanavos2019-07-08 09:08:15 +00:00Commented Jul 8, 2019 at 9:08
-
i need to create a table that contanis so many column, but 60 of that are similar and i want to insert that into an array. There is a limit for the column of a table?Attilio Iurlaro– Attilio Iurlaro2019-07-08 09:09:58 +00:00Commented Jul 8, 2019 at 9:09
-
1@PanagiotisKanavos: actually the SQL standard does define arrays (and Oracle does not have arrays as a datatype in SQL, only in PL/SQL)user330315– user3303152019-07-08 09:10:10 +00:00Commented Jul 8, 2019 at 9:10
-
1@AttilioIurlaro: you need additional tables to store the one-to-many relationships. Please read up on database normalizationuser330315– user3303152019-07-08 09:14:41 +00:00Commented Jul 8, 2019 at 9:14
|
Show 8 more comments
1 Answer
You don't want an array column. You want a separate table (or perhaps a JSON/XML column, but I won't focus on that).
The normal method would be:
create table main (
main_id int identity primary key,
. . .
);
create table element (
element_id int identity primary key,
position int,
value varchar(255),
. . .
);
create table main_elements (
main_element_id int identity primary key,
main_id int references main(main_id),
element_id int references elements(element_id)
);
