jc (ji**********@gm.com) writes:
I am in a situation with an engineering application involving
monitoring of press operations. This involves storage of numbers for
both an X and Y arrays. The number of element within the arrays
varies slightly but should be identical for a singular press
operation.
One approach is to store an element in a row. This would be 6000 rows
(3000 elements/axis * 2). This seems excessive for a single
operation. Also, I have no intention of every reading rows that would
be a subset of an operation.
The other approach is to pack an array of data into a single varbinary
data type.
The latter sounds like a repeating group to me, and that is usually
a gross violation of the fundamentals of a relational database.
However, in the end, it boils down to what you will use this data for.
If you don't plan to use all these values in the database, but only
outside it, storing it as a blob could make sense.
But if you plan to analyse it to find max, mins, average and so on,
storing it in a normalised data model is the way to go.
2) Can people provide some techniques for doing the later (an array
into a single varbinary value)?
Again that depends on how the values would enter the database. It
sounds to me that the easiest would be if the client composed the
binary arrays.
In particular for point 2 how would I cast arrays that vary from
2500 .. 3500 elements with integers to a single varbinary and of
course retrieve?
In which layer? In SQL Server you would have to write a user-defined
aggregate to build the varbinary(MAX). In SQL 2005 you would have to
workaround the restriction that a UDA cannot return more than 8000
bytes.
As for retrieving, look at fixbinary_single:
http://www.sommarskog.se/arrays-in-s...l#fixed-length
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Links for SQL Server Books Online:
SQL 2008:
http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:
http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx