SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Twit List
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 88

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  14:52:36  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  15:16:29  Show Profile  Reply with Quote
I can not find any ANSI ref to NoLock

I CAN find lots of reasons to NOT Use it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  15:18:36  Show Profile  Reply with Quote
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....

A Singleton SELECT into a local variable with many rows...for example



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  15:28:31  Show Profile  Reply with Quote
AH...there it is (WITH UR)

I Never would think of this...as the author states...just plain bad

"You should be scared"

http://www.barkingaardvark.com/sqlblog/?p=50



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/29/2011 :  15:29:02  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  15:30:37  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  16:03:21  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

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?



Maybe that's why IBM Ran away

Kalen's book has some great history...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 12/29/2011 :  23:49:16  Show Profile  Reply with Quote
Against the popular belief NOLOCK does cause blocking.

PBUH

Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/30/2011 :  05:01:54  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
It can, nolock does not actually mean no locks taken.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 12/30/2011 :  07:30:06  Show Profile  Reply with Quote
Beat my head on the NOLOCK wall to no avail. I swear the vendors here DO see PERF_BOOST instead of (WITH NOLOCK).

At least i won with the financial procs

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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/04/2012 :  07:55:51  Show Profile  Reply with Quote
Cursors are faster than outer joins and sub selects!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169374&whichpage=2

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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/04/2012 :  08:48:15  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/05/2012 :  07:02:49  Show Profile  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/05/2012 :  10:50:38  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/05/2012 :  11:01:37  Show Profile  Reply with Quote
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".
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/05/2012 :  11:23:33  Show Profile  Reply with Quote
Then we can agree to agree

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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 01/05/2012 :  12:15:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/05/2012 :  12:38:32  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/05/2012 :  14:08:52  Show Profile  Reply with Quote
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)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 01/05/2012 :  14:29:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you don't want to use spt_values, then add a tally table to your database. It's just a one column table with numbers in it. Mine has values 1-8000, but it depends what you need it for. spt_values is used when a tally table doesn't exist. We use it here on SQLTeam because it's easier to reference that than provide the script to create the tally table with values.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
Page: of 88 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000