10

I am struggling to find any documentation on how SQL Server actually stores a non-persisted computed column.

Take the following example:

--SCHEMA
CREATE TABLE dbo.Invoice
(
    InvoiceID INT IDENTITY(1, 1) PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES dbo.Customer(CustomerID),
    InvoiceStatus NVARCHAR(50) NOT NULL,
    InvoiceStatusID AS CASE InvoiceStatus 
                         WHEN 'Sent' THEN 1 
                         WHEN 'Complete' THEN 2
                         WHEN 'Received' THEN 3
                       END
)
GO

--INDEX
CREATE NONCLUSTERED INDEX IX_Invoice ON Invoice
(
    CustomerID ASC
)
INCLUDE
(
    InvoiceStatusID
)
GO

I get that it is stored at the leaf level, but if the value is not persisted how is anything stored at all? How does the index help SQL Server find these rows in this situation?

Any help greatly appreciated,

Many Thanks,

EDIT:

Thanks to Brent & Aaron for answering this, here's the PasteThePlan clearly showing what they explained.

  • 5
    It is not persisted in the data pages of the table, but it is persisted in the pages of the index. – Aaron Bertrand Feb 7 '17 at 21:09
  • Non persisted Computed columns are not physically stored in the table. They are virtual columns. Their values are recalculated every time they are referenced in a query. see this ref. – Kin Shah Feb 7 '17 at 21:09
11

When SQL Server creates the index on the computed field, the computed field is written to disk at that time - but only on the 8K pages of that index. SQL Server can compute the InvoiceStatusID as it reads through the clustered index - there's no need to write that data to the clustered index.

As you delete/update/insert rows in dbo.Invoice, the data in the indexes is kept up to date. (When InvoiceStatus changes, SQL Server knows to also update IX_Invoice.)

The best way you can see this for yourself is to actually do it: create these objects, and execute updates that touch the InvoiceStatusID field. Post the execution plan (PasteThePlan.com is helpful for this) if you want help seeing where the index updates are happening.

  • 1
    @Uberzen1 No, as he explained, it is written to the index pages at insert/update time. It doesn't have to recompute anything if the index is used to access the column. – Aaron Bertrand Feb 7 '17 at 21:16
  • Ah! I'm with you now, sorry! – Uberzen1 Feb 7 '17 at 21:17
  • 6
    @blobbles well, no offense, but I don't think that's on Brent. They could paste that same XML to dropbox, MSDN forums, here, basically anywhere online... does every online service now need to be responsible for secrets that might be divulged by people who upload files there? – Aaron Bertrand Feb 7 '17 at 21:36
  • 2
    @blobbles yeah, you just can't stop people from oversharing. Hey, by the way, follow me on Instagram - I'm BrentO - and I share pictures of my breakfast there. ;-) – Brent Ozar Feb 7 '17 at 21:45
  • 4
    @blobbles in the Privacy link, it states: Data you copy/paste in here is public. Anyone can read it. There is no security. – ypercubeᵀᴹ Feb 7 '17 at 22:01
8

The value for an indexed, non-persisted computed column is not persisted in the data pages of the table, but it is persisted in the pages of the index. It remains non-persisted in the table, regardless of whether it is persisted in 0, 1, or multiple indexes.

Just to illustrate Brent's description, taking the example you gave, let's insert a row:

INSERT dbo.Invoice(CustomerID, InvoiceStatus) VALUES(1,N'Sent');

Now, let's see the index pages:

DBCC TRACEON(3604, -1);
DBCC IND(N'dbname', N'dbo.Invoice', 2);

(Obviously change dbname, and the index ID might not be 2 in your case.)

Output (yours will surely differ):

enter image description here

And finally, let's inspect the page for PageType 2:

DBCC PAGE(7, 1, 584, 3);

(You will likely need to change 7 to match your database id, and if you have multiple data files, you may need to change the second argument to match PageFID from the first result.)

Output:

enter image description here

That's on the index page.

  • Very cool, thanks Aaron. The reason I asked the question initially is that I am having some real trouble deploying a similar index in the real world, and wanted to understand exactly what is going on under the hood so I can figure out the issue. This helps a lot, thanks! – Uberzen1 Feb 7 '17 at 21:32
  • 1
    @Uberzen1 Can you define "real trouble"? Are you going to post a question about that problem? – Aaron Bertrand Feb 7 '17 at 21:33
  • I may do, I was going to dig into it some more myself first, but just wanted to get my head around what the create index statement is exactly doing. The TLDR is; I have a large table similar to the invoices table above, it has around 400m records and, unfortunately has the OrderStatus column slapped right across the middle of it, making indexing etc. a bit painful. We have added a computed column for now that we will eventually persist and move the varchar field out to it's own table. 1/2 – Uberzen1 Feb 7 '17 at 21:38
  • 5
    @Uberzen1 Yeah, because the computed column is actually materialized onto the disk when writing to the index, all of that activity has to be logged. A workaround might be to stop relying on the computed column - either put that expression into a view or the ad hoc queries, and if that's not an option you could make a new nullable column, update it in chunks (to avoid the log killing), then drop the computed column, rename the new column, and change your DML to manually write that. But really since it's redundant info you can derive from existing data, I would opt for the first option. – Aaron Bertrand Feb 7 '17 at 21:43
  • 2
    Thanks so much Aaron. I'm glad you mentioned putting a view in front of it as that was my go to solution too, perhaps it's time to revisit that idea! – Uberzen1 Feb 7 '17 at 21:47
6

The attribute PERSISTED for a computed column relates to whether the values are persisted in the table (clustered index or heap) and not whether the values are persisted in the index.

The CREATE INDEX has the requirements for the limitations regarding computed columns and indexes:

Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included in non-key columns as long as the computed column data types is allowable as an included column. For more information, see Indexes on Computed Columns.

There is no limitation on whether the computed column is persisted or not.

and further (not about included but about computed columns in the main part of an index):

Indexes can be created on computed columns. In addition, computed columns can have the property PERSISTED. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

To index a computed column, the computed column must (be) deterministic and precise. However, using the PERSISTED property expands the type of indexable computed columns to include:

...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.