Can we have array type data in sql server 2008 currently i am using comma seperated value to be treated as array value
5
-
Why you need array in a sql query? What is your plan?YvesR– YvesR2012-06-22 11:36:57 +00:00Commented Jun 22, 2012 at 11:36
-
1If you need this as a field in another table, it is generally a better idea to create a new table, with one row for each element of the array.Gordon Linoff– Gordon Linoff2012-06-22 14:06:41 +00:00Commented Jun 22, 2012 at 14:06
-
3@GordonLinoff: Not if you have thousands of (rows and) columns in each array.Martin F– Martin F2015-02-27 22:52:21 +00:00Commented Feb 27, 2015 at 22:52
-
@YvesR what is the problem with arrays in SQL?Matthieu– Matthieu2019-10-04 15:03:00 +00:00Commented Oct 4, 2019 at 15:03
-
@Matthieu No problem, just asked to understand the use case at that time before answer, meanwhile he got plenty of answers anyway.YvesR– YvesR2019-10-10 09:32:07 +00:00Commented Oct 10, 2019 at 9:32
Add a comment
|
3 Answers
SQL Server 2005+ supports table-valued variables:
declare @arr table (col1 int)
insert @arr (col1) values (3), (1), (4)
These are equivalent to arrays.
Comments
You can use instead:
1. Table
2. Temporary table
3. Table variable (2005+)
4. Table-valued parameters (2008+)
Comments
User-defined tables:
If you want to learn more, this article is widely referenced:
http://www.sommarskog.se/arrays-in-sql-2008.html
Table-value parameters were introduced in SQL Server 2008.
1 Comment
Haris Osmanagić
How does this help though? I'm looking for options to store arrays in a column too, and it looks like TVPs make it easier to send arrays to SQL server, but do not help solving the problem of actually storing the data.