Read Uncommitted, locks, and transactions

  • SQLRNNR

    SSC Guru

    Points: 281252

    Very good question Craig.

    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

  • mythilimca89

    Newbie

    Points: 7

    i can't understand the question

    :w00t:

  • Cliff Jones

    SSChampion

    Points: 10517

    cengland0 (2/14/2012)


    Cliff Jones (2/13/2012)


    Yes, that was the way I always did it also. It was very fast but then you had to deal with all those pesky named constraints and foreign keys.

    I agree, nicely worded question and answers that required some thought.

    I still do it this way, that's why I asked if there was a better way to do it -- perhaps by using the ALTER VIEW or some other method. So everyone keeps saying that they used to populate tables this way. Which way are you doing it now?

    I only do this method for my raw data sources. The presentation layer (Not a real name for a data layer) is what has all the constraints and foreign keys. The raw data comes from multiple sources so no contraints are at that level. If they were, then you'd have to be extremely careful on which tables you load first and too many tables get populated simultaneously to have to worry about another problem like that.

    I suspect it is still a valid and efficient way of doing it in most situations; I just donโ€™t personally have much occasion to have to do this anymore.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    mythilimca89 (2/14/2012)


    i can't understand the question

    :w00t:

    I assume you're joking? If not, what in particular can't you understand?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Jagadish Kumar Punnapu

    SSChasing Mays

    Points: 645

    A very very good question Craig... Thank you...

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Straightforward question and highly detailed explanations!

    Thank you.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • princa

    SSCommitted

    Points: 1819

    Good question, it's actually the same scenario I am facing right now!!!

    But I still have some questions:

    #1 - I am using a partitioned view to union two tables, one is original table, one is backup table. So every time I will need to backup original table to backup table, and use partitioned view to union them then will return the same result as i query only original table.

    #2 - Then I will delete from original table and then insert all new data. While I am performing this step, eventhough I use with (nolock) on my partitioned view with select statement, it will still be locked until the insert process is done, the same behavior like Truncate.

    Am I doing something wrong here?

  • Evil Kraig F

    SSC Guru

    Points: 100851

    princa (5/7/2012)


    Good question, it's actually the same scenario I am facing right now!!!

    But I still have some questions:

    #1 - I am using a partitioned view to union two tables, one is original table, one is backup table. So every time I will need to backup original table to backup table, and use partitioned view to union them then will return the same result as i query only original table.

    #2 - Then I will delete from original table and then insert all new data. While I am performing this step, eventhough I use with (nolock) on my partitioned view with select statement, it will still be locked until the insert process is done, the same behavior like Truncate.

    Am I doing something wrong here?

    Whats the wait_type on the spid doing the select? I'd have to reconstruct the scenario to confirm. Also, what version is the server?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • kapil_kk

    SSC-Insane

    Points: 21316

    just a wild guess n get +1 for that ๐Ÿ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 9 posts - 46 through 54 (of 54 total)

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