|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
looooooooong recordHi.
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 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 Show quoteHide quote "Empi" <mpe***@softstart.co.il> wrote in message news:uu$Bx4x#JHA.3544@TK2MSFTNGP04.phx.gbl... > 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 > > No need to search.
middle tier app. Thanks! Show quoteHide quote "Peter DeBetta" <debettap hotmail> wrote in message news:BA87E2FC-0B24-4FDC-AC3B-AEB8ECD5731C@microsoft.com... > 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 > > "Empi" <mpe***@softstart.co.il> wrote in message > news:uu$Bx4x#JHA.3544@TK2MSFTNGP04.phx.gbl... >> 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 >> >> Then I'd use varbinary(max).
Show quoteHide quote "Empi" <mpe***@softstart.co.il> wrote in message news:uWNs#O1#JHA.1252@TK2MSFTNGP04.phx.gbl... > No need to search. > middle tier app. > > Thanks! > > > "Peter DeBetta" <debettap hotmail> wrote in message > news:BA87E2FC-0B24-4FDC-AC3B-AEB8ECD5731C@microsoft.com... >> 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 >> >> "Empi" <mpe***@softstart.co.il> wrote in message >> news:uu$Bx4x#JHA.3544@TK2MSFTNGP04.phx.gbl... >>> 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 >>> >>> > > 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. Show quoteHide quote "Empi" wrote: > 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 > > >  
Other interesting topics
FOR XML: How to prevent nesting elements
Obtain Data from XML column Bulk load and XSD problem... Obtaining data from an XML column to a document XML Shreading problem XML Collating sequence changing the results header with for xml Exporting to an XML file Returning position() in the results SQLXML XPath data with apostrophe |
|||||||||||||||||||||||
