Actually, the grotesque-looking aggregation of the attribute/value table
is reasonably fast.
It also gives you a way to enforce TYPES on the values,
which is another real issue with attribute/value format.
The underlying table is still amazingly spacewasteful,
but if you HAVE to ....

CREATE VIEW Normal AS
SELECT key1, key2, ...
min(case when attribute = 'name' then value end) as name,
min(case when attribute = 'asof' then cast(value as
datetime) end) as asof,
...
FROM Abnormal
GROUP BY key1, key2, ...

I can't speak for the MSSQL engine, but proper dragging and beating
in the query tree ought to eliminate any processing to support
unused columns (MIN(...) expressions) when making reference
to the view.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns95428EB79609Yazorman@127.0.0.1...
> Karen Sundquist (karensundq@yahoo.com) writes:
> > One alternative is to create a table containing field-name/value
> > pairs. This is ugly and breaks the ER modelling rules that tell me yo
> > put attributes of an item into the same row. All the same, I would
> > prefer this method to the first approach.
>
> This would be my preference too.
>
> Of course, neither this is going to be fun if you want reports with
> these attributes as columns. You could define views that twisted
> the rows as columns, but with over 100 columns that will be a horrible
> self-join if all attributes are to be included.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp