Persisted vs Non-persisted Computed columns

Question

Persisted vs Non-persisted Computed columns

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Nils Gustav Stråbø (10/7/2010)
You won't believe how many people that actually use NOLOCK or READ_UNCOMMITTED to "fix" concurrency and locking problems. One of our vendors use NOLOCK in every(!) SELECT statement in a system we have purchased from them. They have never bothered to explain the reason for doing it, but my guess, after doing some investigation, is that it is caused by two things:
- Lots of missing indexes, which results in excessive scans
- Bad programming, because when I save data in the front-end, SQL Profiler showed that before the transaction for the update statement was committed, another connection from my pc was opened trying to read the updated row(s).

I worked as a consultant on a system for a major health insurance company and they had the nolock hint on every single query in their system. Keeping in mind that this is the system that pays disbursements both from "paper" claims and real time debit cards our balances were always out of whack because we would authorize payment when there was not enough money due to dirty reads. I can't tell you how many times I suggested that they should find better ways of dealing of locked tables. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rtelgenhoff SSC Eights! Points: 928 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted

'You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED'

--If I wanted to find all those who received a work permit on a give day, this query works but shows a table scan.

SELECT [DOWorkPermit] FROM [Employee_2] WHERE [DOWorkPermit] = '12/31/1993';

--Add an index on the persisted, computed column:

CREATE NONCLUSTERED INDEX IX_WorkPermit ON [Employee_2] ([DOWorkPermit]);

GO

--The query still shows a table scan because DOBirth is in the output list and is needed to find the computed DOWorkPermit day:

SELECT [DOWorkPermit] FROM [Employee_2] WHERE [DOWorkPermit] = '12/31/1993';

-- drop and recreate the index including the DOBirth column

DROP INDEX IX_WorkPermit ON [Employee_2];

GO

CREATE NONCLUSTERED INDEX IX_WorkPermit ON [Employee_2] ([DOWorkPermit]) INCLUDE ([DOBirth]);

GO

--an index scan is now used

SELECT [DOWorkPermit] FROM [Employee_2] WHERE [DOWorkPermit] = '12/31/1993'

My question is, couldn't an index seek be done on the persisted, indexed value rather than having to compute it while doing a scan? Is it because there's only 1 record in the table or am I missing or misinterpreting something...

timothy bates SSC-Addicted Points: 471 More actions · Answer 3

Nice question, I appreciate the intent of the question as I see it, 'Persisted does not mean permanent.'