Mimicing Arrays or dynamic lists in SQL Server 2008

Often we might need to use arrays or dynamic lists in SQL server , we use them all the time in other programming languages without a second thought but we can replicate something similar by using table variables.  

Thanks to Pinal Dave for this great article on same.

This works in SQL server 2008 and upwards. To store a list of ints :

DECLARE @ListofIDs TABLE(IDs int);
INSERT INTO @ListofIDs
VALUES(1),(2),(3),(4),(5),(6);
SELECT IDs FROM @ListofIDs;
GO

Or strings:

DECLARE @ListofIDs TABLE(IDs VARCHAR(100));
INSERT INTO @ListofIDs
VALUES('a'),('10'),('20'),('c'),('30'),('d');
SELECT IDs FROM @ListofIDs;
GO

Or even to replicate a mulit-dimensional array :

DECLARE @Array TABLE(IDs int , Description VARCHAR(100));
INSERT INTO @Array
VALUES(1, 'Sydney'),(2,'Toyoko'),(3,'Dublin'),(4, 'London'),(5,'Madrid'),(6,'Paris');
SELECT * FROM @Array;
GO

Very handy indeed ! For any version earlier than 2008 , since they don't support table variables , you'll have to use unions :

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