Persisted vs Non-persisted Computed columns

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item Persisted vs Non-persisted Computed columns

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, thanks.

    Though I think formatting the code into little sections, and including the commented select statements, makes it a little more confusing and harder to read than it could have been.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • GPO

    SSCarpal Tunnel

    Points: 4556

    Just one question. What's the significance of the (nolock) hint? Could you argue that none of the answers could be guaranteed in the event of a dirty read? Cheers and many thanks for the question.

    GPO

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • Ian Elliott

    SSC Eights!

    Points: 995

    Argh didn't get it wrong because I didn't know the answer but rather because I can't seem to count this morining.:blush:

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Nice question, though maybe a bit too easy.

    Shame about the (nolock) hints, though. They serve no purpose, and they might lead people to believe that these hints are okay to use. They are not, except in a very limited amount of situations.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question.

    I was afraid it was some trick question of some exotic behaviour of SQL Server (with the no lock hints and everything), but luckily common sense was enough to answer the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Hugo Kornelis (10/7/2010)


    and they might lead people to believe that these hints are okay to use. They are not, except in a very limited amount of situations.

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

  • sjimmo

    SSChampion

    Points: 11139

    First - this was a very good question. Learned something today which is great.

    Hugo

    Shame about the (nolock) hints, though. They serve no purpose, and they might lead people to believe that these hints are okay to use. They are not, except in a very limited amount of situations.

    What would these limited situations be?

    Nils

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

    How about thousands of connections hitting against high volume OLTP databases that are also required for up to the minute reporting where replication is not an issue nor are dirty reads?

    There are times where hints are required. I may not like them as the norm, but I don't think that one should dismiss them all together.

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    sjimmo (10/7/2010)


    Hugo

    Shame about the (nolock) hints, though. They serve no purpose, and they might lead people to believe that these hints are okay to use. They are not, except in a very limited amount of situations.

    What would these limited situations be?

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    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

    SSC-Dedicated

    Points: 34566

    Good question.....lots of reading first thing in the AM.

  • Arjun SreeVastsva

    SSCertifiable

    Points: 7135

    Nice question

    I am much afraid by seeing the question

    post this type questions

  • sjimmo

    SSChampion

    Points: 11139

    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ø

    SSChampion

    Points: 11259

    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.

Viewing 15 posts - 1 through 15 (of 18 total)

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