لطفا منتظر باشید ...
The Pièce de Résistance
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.
