Sql-server – Convert columns into rows for use with indexed/materalized view

cross-applymaterialized-viewsql serversql server 2014unpivot

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 UNPIVOT and 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 UNPIVOT or 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 UNPIVOT and 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 INNER JOIN.

Here is the table (I'm showing 6 records to simplify but imagine it's 500K or more records):

UserAuditTbl

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

With 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.

Best Answer

Based on your description, I think it's fairly unlikely that an indexed view is going to be your best solution. The indexed view will add overhead each time you write to UserAuditTbl, and a CROSS APPLY that unpivots each row into two rows should be quite efficient if you are careful to first seek and/or filter to only the necessary rows (e.g., only those rows for a (UserID, UserAttribute) tuple you are interested in).

If you post a full example of your exact table (including indexes), a script to fill your table to the expected number of rows with fake data, and the exact T-SQL you need to optimize, we might be able to provide more specific suggestions.

However, if you decide to try the indexed view approach, one fairly easy way to implement your CROSS APPLY as a join is to create a table that contains one row per value in your CROSS APPLY, then join to that table. For example:

Create the necessary tables

/* Your sample data */
CREATE TABLE dbo.UserAuditTbl (
    RowID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_UserAuditTbl PRIMARY KEY,
    UserID VARCHAR(10) NOT NULL,
    UserAttribute VARCHAR(100) NOT NULL,
    OldValue VARCHAR(100) NOT NULL,
    NewValue VARCHAR(100) NOT NULL
)
INSERT INTO dbo.UserAuditTbl (UserID,UserAttribute,OldValue,NewValue)
VALUES  ('ID00184','FirstName','John','Jon'),
        ('ID00184','LastName','NULL','Albert'),
        ('ID00185','HomePhone','555-555-1122','555-555-1212'),
        ('ID00188','MiddleName','Jesse','James'),
        ('ID00188','CellPhone','555-555-1234','555-555-1555'),
        ('ID19594','ZipCode','00000','90210')
GO

/* Create a two-row table to implement the CROSS APPLY as a join */
CREATE TABLE dbo.OldAndNewValues (
    OldOrNewValue CHAR(8) NOT NULL
        CONSTRAINT PK_OldAndNewValues PRIMARY KEY
)
INSERT INTO dbo.OldAndNewValues (OldOrNewValue)
VALUES  ('OldValue'),
        ('NewValue')
GO

Create the indexed view

/* Create a view that implements the cross apply as a join */
CREATE VIEW dbo.UserAuditIndexedView WITH SCHEMABINDING AS
SELECT u.RowID,
    u.UserID,
    u.UserAttribute,
    v.OldOrNewValue AS Col,
    IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) AS Val
FROM dbo.UserAuditTbl u
CROSS JOIN dbo.OldAndNewValues v
WHERE IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) IS NOT NULL
GO

/* Create the view index.
    You can adjust the column order as necessary for your audit queries.
    Note that the first index on a view must be a unique index,
    so the RowID and Col columns must be used. */
CREATE UNIQUE CLUSTERED INDEX UQ_UserAuditIndexedView
ON dbo.UserAuditIndexedView (UserID, UserAttribute, RowID, Col)
GO

Confirm that the results match

/* Use the view index */
SELECT *
FROM dbo.UserAuditIndexedView WITH (NOEXPAND)
ORDER BY RowID, Col

/* An adapted version of your original query (the original does not compile) */
SELECT RowID, UserID, UserAttribute, Col, Val
FROM dbo.UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), ('NewValue', NewValue)
) x (Col, Val)
WHERE Val IS NOT NULL
ORDER BY RowID, Col
GO
Related Question