I'd like to convert columns into rows for a given
SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. first name, middle name, last name, home number).
I've had success doing so with
CROSS APPLY, respectively. However, I was hoping to improve performance for a large set of records (>500k) and all in one query. The table has a primary clustered index; however, according to the execution plan, for
CROSS APPLY, both are performing a
Nested Loop (Left Outer Join), at a 31% cost. Again, it's not like the query is agonizingly slow, but I'd like to inch out every bit of performance I can achieve.
My thought process was, OK, maybe if I can create an indexed view, the query will run very fast.
The problem is SQL Server
indexed views (materialized views in Oracle-speak) have so many restrictions such as
CROSS APPLY (see the full list here). Since
CROSS APPLY is effectively an
INNER JOIN and joins are acceptable for indexed views, maybe that's at least one good alternative. Even so, I'm having some difficulty rewriting
CROSS APPLY as an
Here is the table (I'm showing 6 records to simplify but imagine it's 500K or more records):
RowID UserID UserAttribute OldValue NewValue 1 ID00184 First Name John Jon 2 ID00184 Last Name NULL Albert 3 ID00185 Home Phone 555-555-1122 555-555-1212 4 ID00188 Middle Name Jesse James 5 ID00188 Cell Phone 555-555-1234 555-555-1555 6 ID19594 Zip Code 00000 90210
CROSS APPLY, the query would look like:
SELECT RowID ,UserID ,Column ,Value FROM UserAuditTbl CROSS APPLY ( VALUES ('OldValue', OldValue), 'NewValue', NewValue) ) x (Column, Value) WHERE Value IS NOT NULL
I guess the first question is can I convert columns into rows such that the underlying query can be incorporated into an indexed view? And the second question is if it's best just to avoid indexed views, then what are my options here? ETL to a new table via SSIS or trigger?
Thank you for your time.