SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Persisted vs Non-persisted Computed columns


Persisted vs Non-persisted Computed columns

Author
Message
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3655 Visits: 3575
Did I ever say that this hint was never to be used? For reporting purposes where the possibility of reading dirty data does not affect the quality of the report then go ahead and use it. I've done it myself many times. What I mentioned in my previous post was misuse due to poor programming.
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8026 Visits: 2629
Good question.....lots of reading first thing in the AM.
Arjun SreeVastsva
Arjun SreeVastsva
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3300 Visits: 1663
Nice question
I am much afraid by seeing the question
post this type questions
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4820 Visits: 2907
Hugo
Mostly queries that report aggregated data for an audience that doesn't care about the exact number, but only wants an indication. If the numbers my query produces will be rounded to the nearest million dollars anyway, I can live with a result that might be a couple of thousand dollars off.

Completely agree, which is why we unfortunately use it everywhere. Our environment is very large and growing. We literally have several thousand users hitting against the OLTP databases making hourly decisions. Unfortunately we are unable to replicate all of the necessary data to other locations for them to review though it is moved to a warehouse daily which is used primarily for trending as well as market basket and many other things. Reports are happening at the same time as OLTP. I appologize if you felt that I was picking, but I just wanted to get you to expand upon your answer. Thank you.

Nils
Did I ever say that this hint was never to be used? For reporting purposes where the possibility of reading dirty data does not affect the quality of the report then go ahead and use it. I've done it myself many times. What I mentioned in my previous post was misuse due to poor programming.

Unfortunately, your initial posting read very definite and you posted the 2 reasons that you felt were the the use of locking hints. If I misread, I am sorry - but how many others?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3655 Visits: 3575
Ok, I can see that my comment about using NOLOCK to fix concurrency issues could be explained a bit better, and that there are legitimate reasons to use it, for example in reporting scenarios.

But I cannot see where I completely condemn the use of NOLOCK. I mentioned that one of our vendors use in in ALL SELECT statements, and most of them has nothing to do with reporting functionality. They are plain SELECTS that returns just a few rows, but because the client app is poorly programmed (as I tried to explain) the have "fixed" it with NOLOCK.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62949 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 366
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
timothy bates
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 124
Nice question, I appreciate the intent of the question as I see it, 'Persisted does not mean permanent.'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search