Using custom types as arrays in T-SQL

One of the missing language features in T-SQL language is array support. In some cases you can use custom types to work with arrays that are passed as parameters to your stored procedures.

Custom types in T-SQL enable you to create alias for some table, .Net or built-in type. Once you create a custom type, you can use it for local variables and parameters of functions.

The following statements create int and string arrays as a tables with one column with specified type:

 create type "int[]" as table (value int);
create type "string[]" as table (value nvarchar(4000));

You can insert or delete rows from this array using standard T-SQL DML operations.

Objects of this type cannot be stored in other tables, but you can use them as parameters of T-SQL functions:

 create function dbo.sum(@array "int[]" readonly)
 returns int
 as begin
 return (select sum(value) from @array)
 end;
 go
create function dbo.avgLength(@array "string[]" readonly)
 returns int
 as begin
 return (select avg(len(value)) from @array)
 end;

Now you can create a local array variable, fill it with some values and pass it to the function as input parameters:

 declare @t as "int[]";
insert into @t values(1),(2),(3);
select dbo.sum(@t);
go
declare @t as "string[]";
insert into @t values('test'),('sql'),('server');
select dbo.avgLength(@t);