SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

I am really enjoying writing about computed column and its effect in terms of storage. Before I go on with this topic, I suggest you read the earlier articles about computed column to get the complete context.

This is the list of the all the articles in the series of computed column.

This article actually originates from the questions asked by one of the smartest attendees I have ever met in training a few weeks ago. He really impressed me with these good questions. He asked me this: “if we create the index on computed column, does it increase the data size of the original table? In other words, does the computed column become persisted if we create an index on it?“

This is a great question and the answer is rather very simple: No. It does not increase the data size of the original table as well as it does not turn the column into persisted. When we create an index on the column of the table, there is an additional space occupied by that index. Let us run the following code and understand this behavior.

Taking a look at the result, it is very clear that columns made persisted only takes additional spaces. On the other hand, when an index is created, it does not increase the data size of the table; rather, the index created just uses up the unused space in the table.

Hi there,
what a about an index on a NON persist computed column.
It’s not a problem at all to add such an index; but what might be the
reason to allow such an index.
One could be is it th computed column is part of an index (via SUBSTR(..)
Do you have any idea why MS supports an on a NON persisted column?

pinaldave,
I am doing a SQL 2008 R2 data conversion. Oneof the tables has 2 computed persistent fields (BodyMassIndex & WtHR – which is waist to hip ratio) when I attempt to import the data after table creation, I get errors. How do I go about importing all the other data and leaving these fields null so they can compute automatically? I have tried using NULL, ”, and several other methods that have not worked. I have found nothing online to assist with this problem.

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Destination Input” (317)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Destination Input” (317)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination – ExamV2″ (304) failed with error code 0xC0209029 while processing input “Destination Input” (317). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – Query” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Well I figured it out after 1.5 days trying everything I could think of…..
The errors were due to abou 15 rows of data that someone has inserted 0.00 into rather than leave it NULL. The calculation for BodyMassIndex could not calculate on a ZERO (0) so it would throw an error and fail to import the remainer of data. I ran an update statement to set the 0.00’s to NULL and the import worked PERFECTLY!

I wanted to post what I found in the event someone else out there runs across the same type of problem.

Well DeAnn…. since the first error message reported “Divide by zero error encountered” it seems strange to me that it took 1,5 days before thinking about that ;-)
On a serious note, it’s a common mistake to overlook error messages, one feels like drowned in them! Read error messages carefully it’s an always valid piece of advice!

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.