| Author |
 Topic  |
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
 Posted - 12/29/2011 : 14:52:36
|
quote:
Originally posted by X002548
Another M$ Hack...(NoLock)
NoLock is not an MS hack. It's an ANSI standard isolation level (READ UNCOMMITTED)
Yes, I'm tired of people using it as the 'go faster' switch.
--
Gail Shaw
SQL Server MVP |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/29/2011 : 15:29:02
|
quote:
Originally posted by X002548
And...I don't know if you know any other platforms....but wouldn't you say the M$ SQL Server has a LOT of hacks....
But some/many may originate from the Sybase original product? |
Edited by - Kristen on 12/29/2011 15:46:42 |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/29/2011 : 15:30:37
|
quote:
Originally posted by X002548
I can not find any ANSI ref to NoLock
Under isolation levels, the lowest isolation level - read uncommitted. Nolock is just a synonym for read uncommitted, nothing more, nothing less.
--
Gail Shaw
SQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 12/29/2011 : 23:49:16
|
Against the popular belief NOLOCK does cause blocking.
PBUH
|
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 12/30/2011 : 05:01:54
|
It can, nolock does not actually mean no locks taken.
--
Gail Shaw
SQL Server MVP |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 01/04/2012 : 08:48:15
|
yeah -- I was going to comment on that thread but I just couldn't bring myself to.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/05/2012 : 07:02:49
|
quote:
Originally posted by DonAtWork
Cursors are faster than outer joins and sub selects!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169374&whichpage=2
I think he might be on to something. I have proven it with an example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169727
(Unfortunately, the OP did not post any sample data to experiment with and see why the set-based query is slower)
Edit: Edited to make it clear that the proclamation that "Cursors are faster than outer joins and sub selects!" was not made by DonAtWork  |
Edited by - sunitabeck on 01/05/2012 10:58:00 |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/05/2012 : 11:01:37
|
quote:
Originally posted by DonAtWork
Cursors have their place. It is a VERY small place. SQL SERVER is geared towards SET BASED operation, not iterative.
And yes, we would need sample data to actually see how that performs, along with any indexes, statistics, etc.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
I remember seeing an example in one of Itzik Ben-Gan's books where cursor-based query was faster than the set-based query. But, as I recall, he too was explicitly stating that "yes, cursors have a place, albeit very small". |
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 01/05/2012 : 12:15:23
|
quote:
Originally posted by DonAtWork
Cursors have their place. It is a VERY small place.
That place is called "after trying absolutely EVERYTHING ELSE first" and it is indeed microscopic.  |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 01/05/2012 : 12:38:32
|
quote:
Originally posted by robvolk
quote:
Originally posted by DonAtWork
Cursors have their place. It is a VERY small place.
That place is called "after trying absolutely EVERYTHING ELSE first" and it is indeed microscopic.
I would like to slightly disagree and propose a change to that statement:
From:
quote:
"Cursors have their place. It is a VERY small place."
To
quote:
"IN PRODUCTION CODE, Cursors have their place. It is a VERY small place."
If you are doing some sort of database admin task on a bunch of different entities then a CURSOR is often a perfectly acceptable way to go about it.
Personally I'd rather see a CURSOR operating over a system view of tables than see a call to the undocumented sp_msForEachTable or similar
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/05/2012 : 14:08:52
|
quote:
Personally I'd rather see a CURSOR operating over a system view of tables than see a call to the undocumented sp_msForEachTable or similar
I wholeheartedly share this sentiment. Not necessarily about using cursors, but about undocumented features. Undocumented features make me nervous and my palms start to sweat
I was even leery about using spt_values. But, I see just about everyone on this forum whose opinions I respect and value using it comfortably. So I have started using it myself (but not without kicking and screaming) |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Topic |
|
Twit List
