Array data type in SQL Server 2005

Hi,

I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can′t find how to fix it. In my stored procedure in PL/SQL exists this lines:

TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();

Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.

Thank you

David

There is no array data type in SQL Server. You can simulate one using the built-in data types and a table (table variable or temporary table or permanent table).

declare @.t_array_col table( i int not null identity primary key, v varchar(50) not null )

insert into @.t_array_col values('a')

insert into @.t_array_col values('b')

....

select v from @.t_array_col where i = 1

select v from @.t_array_col where i = 2

...

Alternatively, you can just have fixed-length strings that represent the entire array and split them using the built-in functions.

declare @.array_str varchar(max)

set @.array_str = replicate(replicate('x', 2), 100) -- array of size 100, value is of fixed length - 2 bytes

select substring(@.array_str, (@.n * 2) + 1, 2) -- n varies from 0 through 99

set @.array_str = stuff(@.array_str, (@.n * 2) + 1, 2, replicate('z', 2)) -- initialize nth element

You can use varbinary(max) to store numeric values although it is tricky to convert from binary to one of the numeric data types & forth.

Also, there are more efficient ways to do this in SQL than trying to simulate arrays. You will get better performance by using set-based logic.