SQL Server does not support arrays or a dynamic length storage mechanism like list. Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case.
Let us say here are the values: a, 10, 20, c, 30, d. Now the requirement is to store them in a array or list. It is very easy to do the same in C# or C. However, there is no quick way to do the same in SQL Server. Every single time when I get such requirement, I create a table variable and store the values in the table variables. Here is the example:
For SQL Server 2012:
DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
INSERT INTO @ListofIDs
VALUES('a'),('10'),('20'),('c'),('30'),('d');
SELECT IDs FROM @ListofIDs;
GO
When executed above script it will give following resultset.
Above script will work in SQL Server 2012 only for SQL Server 2008 and earlier version run following code.
DECLARE @ListofIDs TABLE(IDs VARCHAR(100), ID INT IDENTITY(1,1));
INSERT INTO @ListofIDs
SELECT 'a'
UNION ALL
SELECT '10'
UNION ALL
SELECT '20'
UNION ALL
SELECT 'c'
UNION ALL
SELECT '30'
UNION ALL
SELECT 'd';
SELECT IDs FROM @ListofIDs;
GO
Now in this case, I have to convert numbers to varchars because I have to store mix datatypes in a single column. Additionally, this quick solution does not give any features of arrays (like inserting values in between as well accessing values using array index).
Well, do you ever have to store temporary multiple values in SQL Server – if the count of values are dynamic and datatype is not specified early how will you about storing values which can be used later in the programming.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Dear Pinal
The above script will even work in SQL Server 2008 also as both table variable and grouping sets are available in SQL Server 2008 onwards
Hi PInal,
Given query I tried on “Sql Server 2008 R2″ as well as “Sql Server 2012″ it’s working in both of them.
——————————————————————–
DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
INSERT INTO @ListofIDs
VALUES(‘a’),(’10′),(’20′),(‘c’),(’30′),(‘d’);
SELECT IDs FROM @ListofIDs;
——————————————————————–
In “Sql Server 2005″ it’ll not work.
Nice one…………………
Nice one
For multi datatype,one can use sql_variant datatype supported since 2000.
Though I have never use this.I read it has some limitation.
http://msdn.microsoft.com/en-us/library/aa223955%28v=sql.80%29.aspx
So when should i choose sql_variant over varchar ?what is the advantage of sql_variant ?
If for multidatatype varchar is use then to manipulate those data ,one has to use isnumeric,isdate etc. and do manipulation,which is little lengthy process.Beside this one cannot find the exact other datatype beside these two isdate and isnumeric.So process is lengthy and complicated.
In case of Sql_variant,same thing is very easy.We can use sql_variant_property to do so.
declare @t table(col1 sql_variant )
insert into @t values(1),(’2′)
Select sql_variant_property(col1, ‘BaseType’) from @t
vs
declare @t table(col1 varchar(100) )
insert into @t values(1),(’2′)
Select sql_variant_property(col1, ‘BaseType’) from @t
Yes, it works only with SQL Server 2008 onwards.
Hi Pinal ,
If values are stored in variable then how above script.works.
e.g:
Declare @var NVARCHAR(max)
@var = ‘a,b,c,d’
We can also get a similar result using CTE
Declare @stringvar VARCHAR(max)
set @stringvar = ‘a,10,20,c,30,d’;
with cte(IDs,stringvalue,id)
as
(
select cast(” as VARCHAR(MAX)), @stringvar, 0
union all
select left(stringvalue,charindex(‘,’,stringvalue,0)-1), right(stringvalue,len(stringvalue)-charindex(‘,’,stringvalue,0)), id+1 from cte where charindex(‘,’,stringvalue,0)>0
union all
select stringvalue,”, id+1 from cte where charindex(‘,’,stringvalue,0)=0 and stringvalue”
)
select IDs from cte where IDs”
in above script, not equal sign is not displayed in last statement & last union statement