Re: Store data horizontally or vertically?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 12 Nov 2005 16:48:59 +0000 (UTC)
serge (sergea@xxxxxxxxxxxxxxxxx) writes:
> My other problem is each stored procedures' returned
> result could fall into any of these four categories:
> - 1 row, 1 column
> - 1 row, many columns
> - many rows, 1 column
> - many rows, many columns
>
> So far I thought about 3 ways of storing the data:
> 1- 1 large table with 50 columns that can hold various
> number of input parameter values. Similar type of table for
> holding the output result for the four categories above.
> 2- 1 small table holding one value per row. All kinds of joins
> with other tables to indicate which SP and which column the
> value belongs to...
> 3- 1 individual table per stored procedure, this way the number
> of columns in the table would match exactly the number of input
> parameters.
>
> Obviously the above 3 categories could apply both for the input
> and output data.
>
> Just to give some numbers: possibly a few thousand SPs and very
> likely each SP would have more than one test scenario.
At the one extreme, one does a full-fledged data model with a table
store_procedures that defines the procedure as such. Then there is a
sub-table that defines the output columns, and then there is one that
holds with rows with output data. Note here that there would be one row for
each value. That is, if an SP returns 20 columns, one row in the result
set results in 20 rows in this table. It will be akweard to do this
transposing. For the input data you would have a similar arrangement,
although somewhat simpler, as parameters do not come in rows.
The other extreme is to say that each procedure is an object of its own,
and thus needs a table of its own. Here output rows can be stored as
rows. But with the number of tables you indicate, this will be quite
unmanagable. Then again, while the first extreme has fewer tables, there
is still the same amount of data to manage.
What I don't like is some sort of a middle ground as you indicate in
your first alternative. Somewhere you will need to store that colunm 25
for procedure A has the data type datetime. Then again, this is probably
the model that makes the test engine easiest to implement.
Steve indicated in his reply that a relational database may not be the
best data store for this situation. And maybe he is right. One solutinn
that has some appeal to me is to use XML. Each XML document would then be
the output from a stored procedure. Parameters could be handled in the same
way, but regular table storage may still work for them, as they have one
dimension less.
In SQL 2005 there is a native xml data type which simplifies this
implementation, but you coudl use ntext in SQL 2000 for the XML
docuemnts. You would have to get all data to client level for comparisons
though.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Store data horizontally or vertically?
- From: serge
- Store data horizontally or vertically?
- Prev by Date: Re: Need security advice on xp_cmdshell, bcp, xml procedure
- Next by Date: Re: Filegroup restore problems
- Previous by thread: Re: Store data horizontally or vertically?
- Next by thread: Something Basic
- Index(es):
Relevant Pages
|
