Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Read Uncommitted, locks, and transactions Expand / Collapse
Author
Message
Posted Sunday, February 12, 2012 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 6,253, Visits: 7,427
Comments posted to this topic are about the item Read Uncommitted, locks, and transactions


- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1250898
Posted Sunday, February 12, 2012 9:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 13,720, Visits: 10,675
Nice question Craig, thanks!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1250914
Posted Sunday, February 12, 2012 6:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 6,253, Visits: 7,427
Koen Verbeeck (2/12/2012)
Nice question Craig, thanks!


Thanks. I was concerned that I wouldn't explain the components well enough. Hopefully the scenario is clear and this doesn't end up as an anti-NOLOCK discussion instead of one regarding troubleshooting the differences between DELETE and TRUNCATE.

Also kind of surprised, this wasn't supposed to be available until tomorrrw.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1250974
Posted Monday, February 13, 2012 1:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 11,194, Visits: 11,165
Good question, no complaints from me



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1251025
Posted Monday, February 13, 2012 1:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:40 AM
Points: 1,332, Visits: 484
Nice question, i learned something again.
Post #1251026
Posted Monday, February 13, 2012 1:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 5,998, Visits: 8,261
Great question. I especially liked how you explicitly included the rationale for (and dangers of) using NOLOCK.
Thanks!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1251030
Posted Monday, February 13, 2012 2:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 13,720, Visits: 10,675
Evil Kraig F (2/12/2012)
Koen Verbeeck (2/12/2012)
Nice question Craig, thanks!


Thanks. I was concerned that I wouldn't explain the components well enough. Hopefully the scenario is clear and this doesn't end up as an anti-NOLOCK discussion instead of one regarding troubleshooting the differences between DELETE and TRUNCATE.


It is explained well enough - at least according to me - and the MSDN article linked to also explains it all.
Your question did say the end users decided they could live with incomplete or missing data, so an anti-lock discussion is not necessary: the DBA/report developer did his job in my opinion. He informed the users about the consequences and they accepted them.

Evil Kraig F (2/12/2012)

Also kind of surprised, this wasn't supposed to be available until tomorrrw.


That's because is used DBCC_TIMEWARP to solve this question
Ok seriously now, in the weekend questions usually appear a few hours too early.





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1251042
Posted Monday, February 13, 2012 2:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 1,688, Visits: 1,109
I liked this question - partially at least because I could recall the answer from double checking the answer to the last truncate question.
Post #1251059
Posted Monday, February 13, 2012 2:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
This was a good question. I like how it was written in the terms of a real problem to solve.

What other ways can you change the entire contents of a table and still have the reports available without dirty data?

I've thought of creating a view that points to the table. Then, you can populate another table with the new information and then update the view to point to the new table. Once all the queries are done running on the old dataset table, the old table can then be deleted. I've never tried this so I'm curious what the experts on this forum have to say about this. Does the view get locked if a query is running it? Any negative sides to my proposed solution?
Post #1251060
Posted Monday, February 13, 2012 2:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 7:08 AM
Points: 1,659, Visits: 2,089
Thank you.
Great question and I really appreciate the time and effort you took in creating the scenario, rather than just posting up the code and asking what's going on.
Post #1251061
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse