looooooooong record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Empi - 02 Jul 2009 15:05 GMT

Hi.

I need to store a record with 16 fields, each with up to 360,000 values of a
2 bytes integer.
Storing each value in a field is quite a crazy
idea(value1,value2,...,value360000),
so, I wonder. I thought of storing each sample in a huge string with comma
as delimiter and parse it each time I need it.
Is there a better way?
Can I store a filled object such as arraylist in a table as is and fetch it?

Thanks.

Empi

Reply to this Message

Peter DeBetta - 02 Jul 2009 18:00 GMT

Without knowing more, varbinary(max) and a function to extract the
individual value would work, or better yet, a user-defined type with a built
in method to do this.

Do you need to do anything else like search for one of the 2-byte ints
within the column?
Who will consume the data (other T-SQL or only a middle-tier app)?

--Peter

> Hi.
>
[quoted text clipped - 11 lines]
>
> Empi

Reply to this Message

Empi - 02 Jul 2009 21:29 GMT

No need to search.
middle tier app.

Thanks!

> Without knowing more, varbinary(max) and a function to extract the
> individual value would work, or better yet, a user-defined type with a
[quoted text clipped - 21 lines]
>>
>> Empi

Reply to this Message

Peter DeBetta - 08 Jul 2009 18:20 GMT

Then I'd use varbinary(max).

> No need to search.
> middle tier app.
[quoted text clipped - 26 lines]
>>>
>>> Empi

Reply to this Message

Bob - 08 Jul 2009 22:09 GMT

You could store it as xml. Then parsing would be easy using the methods of
the xml data-type, eg nodes, query, value, exist. Demo.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO
CREATE TABLE #tmp ( id INT IDENTITY PRIMARY KEY, big_list XML )
GO

-- Generate some dummy data
DECLARE @xml XML

;WITH cte AS
(
SELECT 1 x, 1 y
UNION ALL
SELECT x + 1, y + CAST( RAND() * 100 AS INT )
FROM cte
WHERE x < 100
)
SELECT @xml =
    (
    SELECT y AS value
    FROM cte
    FOR XML RAW(''), ROOT('record'), ELEMENTS
    )
OPTION ( MAXRECURSION 0 )

INSERT INTO #tmp SELECT @xml
GO 10

-- List all values for each id
SELECT t.id, x.y.value('.','INT')
FROM #tmp t
    CROSS APPLY big_list.nodes('record/value') x(y)

-- List ids with value > 7000
SELECT *
FROM #tmp t
WHERE big_list.exist('record[value > 7000]') = 1

Of course you could just store the values pivoted, a bit like the first
resultset from the above query.

> Hi.
>
[quoted text clipped - 10 lines]
>
> Empi

Reply to this Message