To grasp how powerful this new array functionality is, let's take it out for a spin. Here's a simple Transact-SQL script that uses our new array functionality to create, manipulate, list, and destroy an array:
DECLARE @hdl int, @siz int, @res int SET @siz=1000 -- Create the array and return its handle and length SET @hdl=fn_createarray(@siz) SELECT @hdl, fn_arraylen(@hdl) -- Set elements 10 and 998 SELECT @res=fn_setarray(@hdl,10,'test10'), @res=fn_setarray(@hdl,998,'test998') -- Get element 10 SELECT fn_getarray(@hdl,10) -- Get element 998 SELECT fn_getarray(@hdl,998) -- List the array SELECT * from ::fn_listarray(@hdl) SET @res=fn_destroyarray(@hdl)
(Results abridged)
----------- ----------- 13910056 1000 ------------------------------------------- test10 ------------------------------------------- test998 idx value ------- ---------------------------------- 2 3 4 5 6 7 8 9 10 test10 11 12 13 14 15 16 ... 995 996 997 998 test998 999 1000 (1000 row(s) affected)
As you can see, using arrays in Transact-SQL is now as easy as calling a function. And because arrays are accessible via functions, we can easily use them with table values. Here's an example:
DECLARE @h int, @res int, @arraybase int -- Create the array SELECT @h=fn_createarray(1000), @arraybase=10247 -- Load all the Order dates into it SELECT @res=fn_setarray(@h,OrderId-@arraybase,OrderDate) FROM Northwind..orders -- List an array element SELECT idx+@arraybase AS OrderId, value AS OrderDate FROM ::fn_listarray(@h) WHERE idx=10249-@arraybase -- Destroy it SET @res=fn_destroyarray(@h)
In this example we load the OrderDate column for all the orders in the Northwind Orders table into an array. We set up @arraybase so that we can use OrderId as the array indexer (Northwind's OrderIds happen to start at 10248, so subtracting 10247 from each one gives us a one-based array). On the machine on which I'm writing this book, the array takes less than half a second to load. Once it's loaded into memory, we then use the fn_listarray() table- valued function to locate a specific order in the array.