Because array elements can store nearly any type of data, they can also store handles to other arrays. This means that you can easily set up multidimensional arrays and that these arrays can be of either the jagged or the smooth variety. Here's an example:
DECLARE @yhdl int, @xhdl int, @xsiz int, @ysiz int, @res int, @xcnt int, @ycnt int SELECT @ysiz=20, @xsiz=10 -- Allocate the y dimension SET @yhdl=fn_createarray(@ysiz) -- Allocate and fill each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN SET @xhdl=fn_createarray(@xsiz) SET @res=fn_setarray(@yhdl,@ycnt,@xhdl) SET @xcnt=1 WHILE @xcnt<=@xsiz BEGIN SET @res=fn_setarray(@xhdl,@xcnt,RAND()*100) SET @xcnt=@xcnt+1 END SET @ycnt=@ycnt+1 END -- List each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN PRINT 'Listing row: '+CAST(@ycnt AS varchar) SET @xhdl=CAST(fn_getarray(@yhdl,@ycnt) AS int) SELECT * FROM ::fn_listarray(@xhdl) SET @ycnt=@ycnt+1 END -- Get a value using x-,y-coordinates SELECT fn_getarray(CAST(fn_getarray (@yhdl,16) AS int),9) AS 'Element at [9,16]' -- Deallocate each row SET @ycnt=1 WHILE @ycnt<=@ysiz BEGIN SET @xhdl=CAST(fn_getarray(@yhdl,@ycnt) AS int) SET @res=fn_destroyarray(@xhdl) SET @ycnt=@ycnt+1 END -- Deallocate the y dimension SET @res=fn_destroyarray(@yhdl)
(Results)
Listing row: 1 idx value ----------- ------------------------ 28.7541 2 89.3502 3 3.53946 4 23.5332 5 86.0147 6 65.5272 7 55.1878 8 28.106 9 54.9643 10 45.7077 Listing row: 2 idx value ----------- ---------------- 62.1757 2 88.8092 3 83.9364 4 48.1814 5 46.8372 6 11.51 7 66.9179 8 51.3207 9 87.2797 10 3.83372 Listing row: 3 idx value ----------- ---------------- 60.2789 2 4.09385 3 37.9455 4 2.57299 5 52.0562 6 70.8885 7 47.8154 8 54.1449 9 59.3168 10 87.9367 Listing row: 4 idx value ----------- -------------------- 24.996 2 94.1695 3 99.9406 4 18.491 5 87.2228 6 30.5012 7 21.4947 8 68.7588 9 78.544 10 80.717 ... Listing row: 16 idx value ----------- ------------------ 72.5644 2 11.6483 3 98.446 4 63.0639 5 64.6387 6 71.462 7 18.1232 8 69.4337 9 14.1641 10 12.0571 Listing row: 17 idx value ----------- ------------------ 12.8128 2 49.1211 3 44.5183 4 97.7341 5 79.8344 6 94.7446 7 93.0003 8 63.0217 9 31.3682 10 41.8146 Listing row: 18 idx value ----------- ----------------- 33.1187 2 23.9623 3 22.8832 4 15.6967 5 88.0725 6 31.4168 7 71.7862 8 99.8463 9 70.8513 10 99.2734 Listing row: 19 idx value ----------- --------------- 66.302 2 26.2505 3 30.7053 4 16.8188 5 25.4275 6 46.9594 7 39.897 8 36.4633 9 18.7707 10 15.3608 Listing row: 20 idx value ----------- ------------------ 57.3852 2 70.897 3 4.85462 4 17.4024 5 28.5141 6 92.8508 7 19.5683 8 50.7395 9 89.062 10 94.2366 Element at [9,16] ----------------------------- 14.1641
In this example, we allocate the y dimension of a two-dimensional array up front, then enter a loop in which we allocate each row separately. We fill each row with a series of random floating point values, although we could just as well have stored practically any series of values, including those from tables, as we've seen.
We store the array handle from each row allocation as an element in the y array. This allows us to then loop through the y dimension and list each row. It also allows us to access an element using x-,y-coordinates. Because SQL Server doesn't allow us to implicitly cast a sql_variant as an integer, we have to do so explicitly using CAST(), but this is a minor inconvenience.
It should be obvious by now that you can have as many array dimensions as you want, and that these dimensions can be either jagged (varying numbers of elements) or smooth.